Search code examples
ssasmdxolapolap-cube

MDX to filter on a specific hiearachy and move (lag) on another hierarchy

I've some trouble with MDX to filter on a specific hiearachy and move (lag) on another hierarchy.

I've a dimension FinancialYear with 2 attributes : 

Year (2014,2015,2016,2017,2018) 
Active (yes,no)

Here are the underlaying datas :

year/active
2014:no
2015:no
2016:yes
2017:no
2018:no

I would like to create a set with the previous year of the active yes. --> I want 2015.

Solution

  • The following code must be the way to go:

    {[FinancialYear].[Year].[Year].Members * [FinancialYear].[Active].&[yes]}.Item(0).Item(0).Lag(1)