it’s a common requirement to create a report that shows last N days of a business metric – so I thought I’ll post a template here for SQL server analysis server’s MDX query:
MEMBER [Measures].[Sales_last_15_days] AS
MEMBER [Measures].[CurrDate] as
"[Calendar].[Date YYYYMMDD].[" + Cstr(Year(Now())*10000+month(now())*100+day(now())) +"]"
} ON COLUMNS
Here are things that you’ll need to adjust to make it work for your scenario:
1. Date Dimension Attribute & it’s format. The example shows yyyymmdd but you could have different format of the date.
2. Measure name. Instead of [Measures].[Sales] you’ll have to replace it with your business metric. Also, make sure you are using the right aggregate function, in the example above I have used SUM but you’ll have to change this based on your requirement.
3. Create a parameter and use it in index for the Lag function.
4. change [cubename] to your cube name.
I hope this gives you a good starting point to create last N days for your business metric.