Imagine the following scenario: a user wants a query to return the sales value from the past N years (lets say 5). It’s quite an easy query, your rows clause would look like this:

SELECT {[Measures].[Sales]} ON COLUMNS,
([Time].[2005].Lag(4) : [Time].[2005]) ON ROWS
FROM [SteelWheelsSales]

(As we’re using our good old friend SteelWheelsSales, lets pretend we’re back in 2005; If you’d like to have SteelWheelsSales updated, say to cover 2013-2015, please vote on the JIRA we created)

Here lies the problem: SteelWheelsSales only has data from 2003 to 2005, so the member [Time].[2005].Lag(4), which would be 2001, doesn’t exist. As such, the first member of that range doesn’t exist and the row set is empty.

And that’s not nice. It would be good to have a query that goes back N years in time but if there’s not enough data it still displays something.

What we need is a dynamic date range. It goes as far back as 4 years ago or, if there aren’t enough years in your database, shows all years.

Here’s what such a query would look like:

WITH
Set YEARS as [Time].[Years].Members
Member [Measures].[Year Number] as Rank([Time].[2005], YEARS ) - 1
Member [Measures].[Lag] as Iif( [Measures].[Year Number]<4, [Measures].[Year Number], 4) SELECT [Time].[2005].Lag( [Measures].[Lag] ) : [Time].[2005] ON ROWS, [Measures].[Sales] ON COLUMNS FROM [SteelWheelsSales]

Here's how it works:

• The Year Number measure determines the rank of the last year of the date range; we subtract 1 to make it zero based;
• The Lag measure checks whether the last year's rank is at least 4. If it's not, it takes the zero based rank (which means, it takes the first available year as the start of the date range
• The date range on Rows takes this Lag measure to set the beginning of the range

This query returns all three years available in SteelWheelsSales. However, if more periods are available, it'll display up to 5 years of data.

And we can parametrize the query to take a year number:

WITH
Set YEARS as [Time].[Years].Members
Member [Measures].[Year Number] as Rank([Time].[${year}], YEARS ) - 1
Member [Measures].[Lag] as Iif( [Measures].[Year Number]<4, [Measures].[Year Number], 4) SELECT [Time].[${year}].Lag( [Measures].[Lag] ) : [Time].[${year}] ON ROWS, [Measures].[Sales] ON COLUMNS FROM [SteelWheelsSales]

Bonus points: make it smarter, that is, make the query dinamycally return a range of members of the time dimension, regardless of the level of the parameter:

WITH
Set MEMBERS as ${timeParameter}.Level.Members
Member [Measures].[Member Number] as (Rank(${timeParameter}, MEMBERS) - 1)
Member [Measures].[Lag] as IIf(([Measures].[Member Number] < 4), [Measures].[Member Number], 4) SELECT {[Measures].[Sales]} ON COLUMNS, (${timeParameter}.Lag([Measures].[Lag]) : ${timeParameter}) ON ROWS FROM [SteelWheelsSales]

This query takes a parameter ${timeParameter} as a fully qualified MDX member, e.g. [Time].[2004] or [Time].[2004].[QTR1] or [Time].[2004].[QTR1].[Jan] and returns a row set with 5 time periods, or a smaller range if we don't have that much data in the past.