2

Lets say I have a time dimension with the levels all, year, quarter, month, day. I have some sales per month and a calculated measere [sales last year]

WITH MEMBER [Measures].[sales last year] AS (ParallelPeriod([Time].[Time].[Year],1,[Time].[Time].CurrentMember), [Measures].[sales])
SELECT {[Measures].[sales], [Measures].[sales last year]} ON 0
, [Time].[Time].[Month].Members ON 1
FROM [Sales]

Now the time dimension has a visible UnknownMember and it shows as [sales last year] the same value I see for january of my last year in the hierarchy.

Why is that so? How can I avoid it? I mean the sum of all [sales last year] should be the same as the sum of all [sales] over all years except the first.

3

The way `PARALLELPERIOD' works is so that it moves up the hierarchy up to the specified level and then moves back or forward the number of periods, then moves down again using the same path.

In this 'moving down the path' parallelperiod will descend the same member ordinal again. So if it moved up from the first member (in your example january) it will move down the first member again. If it moved up the second member (in your example february) it will move down the second member again.

This of course breaks with an unknown hierarchy, since each child is the first member of it's parent.

So lets say for example you have this hierarchy

     2015             unknown
jan   feb    mar      unknown

And then you do a parallelperiod on the leaf member of the unknown hierarchy, the parallelperiod will move up to the year level of the unknown hierarchy, move back a year and move down to the leaf level again, remembering it moved up from the first member of the unknown hierarchy and moving down the first member of the 2015 hierarchy, which is jan 2015.

  • Good explaination, thanks! Do you know a simple formula that leaves values for unknowmember null but goes back one year for each other time-dimension-member? – Stefan Wilms Jul 2 '15 at 11:52
  • you could probably get away with iif([time].[hierarchy].currentmember = [time].[hierarchy].UnkownMemner, null, parallelperiod(...)) but i'm not able to test that right away – Tom V - try topanswers.xyz Jul 2 '15 at 11:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.