(using SSAS 2008r2, working in Bids)
I've run into a confusing issue regarding the interaction between a calculated time period member and a calculated semi additive measure.
I have a "Time Period" dimension built along the lines of the article "A different approach to time calculations" which allows users to filter measures to "Last Week", "Last Month" etc.
The code for these calculated members looks like this:
Scope([Time Period].[LW]); This = (ParallelPeriod([Time].[Date Hierarchy].[Week], 1, [Time].[Date Hierarchy].currentmember) , [Time Period].[Current]); End Scope;
(obviously month and year have a different first parameter for the parallelPeriod :) )
These work fine for pretty much all the measures I have, the majority of which are just sums of table values etc., except the stock measures.
We store stock data as movement values (2 in, 1 out, 5 in etc.) and the total stock level at any point in time is the sum to date. These are achieved through calculations like the following:
Create Member CurrentCube.[Measures].[Stock Quantity] as sum ( null:tail(descendants([Time].[Date Hierarchy].CurrentMember, [Time].[Date Hierarchy].[Date])).item(0) , [Measures].[Stock Movement Quantity] )
Now to the problem. Time is setup following the 4-4-5 week months pattern with 53 weeks every 4 years.
When using "Last Week" from the Time Period dimension the calcuations work as expected, no issues at all.
When using "Last Month" it looks like the "sum to date" calculation is missing the week 5 stock movement values (i.e. it is giving sum of stock movements in week 1s, week 2s, week3s, week4s to date and excluding all stock movements in week 5s). When using "Last Year" it is doing the same thing but excluding only the stock movement values in week 53 (it is including the week 5 stock movements when using "Last Year"). However if I use the following member in a query:
member [Measures].[Test] as sum ( null:tail(descendants(parallelperiod([Time].[Date Hierarchy].[Month], 1, [Time].[Date Hierarchy].CurrentMember), [Time].[Date Hierarchy].[Date])).item(0) , [Measures].[Stock Movement Quantity])
it works correctly. So it looks like the issue is when the [Time Period].[LW] or [Time Period].[LY] dimension members are combined with the sum to date [Measures].[Stock Quantity] calculation...
So I think I know where the issue is, anyone seen this before or can give some advice on how to fix it? (and if possible why? need it to work but I'd love to know where I went wrong here!!)