Problem Statement:

One of the common business requirement for Dashboards is to be able to see last/rolling 12 month trend on a chart. To meet this requirement, you’ll need to get a data set from the data source and if it’s a SSAS cube, How do you get last/rolling 12 months using MDX?

Note: It’s not a rolling 12 month sum. If you’re looking to do that, please refer: Sum of Last N Years, Quarters, Months, Periods.

Solution:

Here’s the pseudo code, I hope it gives you a good starting point:

[code language=”sql” gator=”false”]
SELECT NON EMPTY
{
[Measures].[MeasureName1],
[Measures].[MeasureName2]
}
ON COLUMNS,
{
[Fiscal Period].[Fiscal Period]
}
ON ROWS
FROM
(SELECT STRTOMEMBER(@FiscalPeriod).Lag(12):STRTOMEMBER(@FiscalPeriod) on 0 FROM [CubeName])

[/code]

Note the use of sub select & query parametrization. You’ll need to pass in current month value in the fiscal period parameter. I hope this helps!