Time Calculation + Measure Calculation combined giving incorrect answer (SSAS)

Question

(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!!)

FrankPl FrankPl · Accepted Answer · 2014-03-19T16:55:02

ParallelPeriod only works well if the number of cousins is the same across a level. It works something like this: go up from the current member to the target level, each time remembering the position the member has within its parents children. Then move back so many members as the second argument states, and then go down to the start level, each time taking the child position into account. Thus, in a four week month, it will lead you from the fourth week to the fourth week, and cannot lead you to the fifth week.

In your case where the number of children can differ at each level by one, maybe a logic somehow like "if the current member is the last child of its parent, use the last child of the period back, otherwise use ParallelPeriod" would be more appropriate, i. e. something like:

Scope([Time Period].[LW]);
    This = (IIf([Time].[Date Hierarchy].CurrentMember IS [Time].[Date Hierarchy].CurrentMember.Parent.LastChild,
               // assuming [Time].[Date Hierarchy].currentmember is a child of a member on the week level
               [Time].[Date Hierarchy].CurrentMember.Parent.PrevMember.LastChild
              ,ParallelPeriod([Time].[Date Hierarchy].[Week], 
                               1, 
                               [Time].[Date Hierarchy].currentmember)
               )
               , [Time Period].[Current]);
End Scope;

I did not test the above code, and it may need some adaptations to get error proof, but you should get the idea.

Time Calculation + Measure Calculation combined giving incorrect answer (SSAS)

1 Answers