MDX - Retrieve last month of prior year value

Question

I have a date hierarchy of Year->Qtr->Month->Day

I am trying to retrieve value for last month of prior year (i.e. Dec 2020). Basically all months of 2021 will be compared to value as it was Dec of 2020.

The following gives me exactly what I need, however I don't want to explicitly put in the member for 2021. I want to use whatever year the user is viewing. I have tried currentmember with no luck as I get nothing.

SSAS Calculation:

sum(ClosingPeriod ([Calendar].[Time].[Month],[Calendar].[Time].[Year].&[2021].lag(1)),
[Measures].[Compliance Pct])

Tried this with no luck:

sum(ClosingPeriod ([Calendar].[Time].[Month],[Calendar].[Time].[Year].currentmemeber.lag(1)),
[Measures].[Compliance Pct])

FrankPl FrankPl · Accepted Answer · 2021-07-21T22:24:59

[Calendar].[Time].[Year] looks like a level, not a hierarchy. And there is a CurrentMember per hierarchy, not per level in MDX. In order to get to the current year member independent on which level of the [Calendar].[Time] the current member is, you can use

Ancestor( 
     [Calendar].[Time].CurrentMember,
     [Calendar].[Time].[Year]
).lag(1)

instead of

[Calendar].[Time].[Year].currentmemeber.lag(1)

The Ancestor(...) expression takes the year level member that is on the level of the current member or above it, see the documentation of Ancestor. It will not work if the current member of the hierarchy is the All member, as that would be on the level above the years, not below or on the level. But then your calculation does not make sense anyway.

And, by the way, it is currentmember, not currentmemeber.

MDX - Retrieve last month of prior year value

1 Answers