Getting year-end values from prior year on each result row of MDX query

1
votes

I have a snapshot fact table with a time grain of monthly and so the measure values are only point in time, not cumulative over time. I need to derive a Previous Year-End value to compare any given month's values to the end of the previous year like so:

| Month | Val1 | PrevYEVal |

| 2014-10 | 101 | 100 |

| 2014-11 | 103 | 100 |

| 2014-12 | 105 | 100 |

| 2015-01 | 110 | 105 |

| 2015-02 | 115 | 105 |

| 2015-03 | 113 | 105 |

...

| 2015-12 | 120 | 105 |

| 2016-01 | 130 | 120 |

etc...

I'm using SSAS and the time dimension table is set up as such with a Year->Quarter->Month->Day hierarchy.

I can find solutions to a previous month, a rolling number of months and getting a YTD cumulative value but this is a straight up point in time to point in time comparison.

I'm trying to use Lag, Ancestor, and/or ParallelPeriod but I can't seem to get the above result set.

1
ssasmdxmsbicube-script

1 Answers

2
votes

Try:

Create member CurrentCube.[Measures].[PrevYEVal] as null;
Scope([Date].[Calendar Hierarchy].[Month].Members);
  [Measures].[PrevYEVal]=([Date].[Calendar Hierarchy].CurrentMember.Parent.Parent.PrevMember.LastChild.LastChild, [Measures].[Val1]);
End scope;