Stock Levels in Cube

[Dewald]

I'm trying to perform a custom rollup for the Time dimension so that Stock level is not totalled over time (it's a non-additive measure). If I select a Day, I want to display that day's Stock level. If I select a certain Week, I want the Stock level of the last day of that specific week and if I select a Month, I want the last day of that month's stock level and so on for Quarter and Year.

I used (Measures.[Quantity], ClosingPeriod([Week])) and it does give me the right value, but how do I do it for Month, Quarter and Year all together in one Calculated Member ?

Hope someone can help !!
Thanks
D

 

[Kelee]

This prevents the quantities from totaling and brings the last day’s/week’s, etc number into the total. This needs to go in the cube’s time dimension under Advanced Properties, Custom Rollup Formula, on each level except the lowest one – e.g.

Year [Time].currentmember.Lastchild
Quarter [Time].currentmember.Lastchild
Month [Time].currentmember.Lastchild
Week [Time].currentmember.Lastchild
Day

If you put it on the day level you get a cube full of errors. If you have empty quantities and you want to bring the last populated amount in, use something like the following on each level:

iif(([Time].currentmember.lastchild is null) or (LEN([Time].currentmember.lastchild) = 0 ),CoalesceEmpty([Time].currentmember.lastchild.lag(1),[Time].currentmember.lastchild.lag(2),[Time].currentmember.lastchild.lag(3),[Time].currentmember.lastchild.lag(4),[Time].currentmember.lastchild.lag(5),[Time].currentmember.lastchild.lag(6),null),[Time].currentmember.lastchild)

Good luck...