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.
Here’s the pseudo code, I hope it gives you a good starting point:
[code language=”sql” gator=”false”]
SELECT NON EMPTY
[Fiscal Period].[Fiscal Period]
(SELECT STRTOMEMBER(@FiscalPeriod).Lag(12):STRTOMEMBER(@FiscalPeriod) on 0 FROM [CubeName])
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!
One thought on “SQL Server Analysis Services MDX: How to display last/rolling 12 months trend in MDX?”