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:
[code language=”SQL”]
WITH
MEMBER [Measures].[Sales_last_15_days] AS
Sum
(
{
[Calendar].[Date YYYYMMDD].CurrentMember.Lag(14)
:
[Calendar].[Date YYYYMMDD].CurrentMember
}
,[Measures].[Sales]
)
MEMBER [Measures].[CurrDate] as
"[Calendar].[Date YYYYMMDD].[" + Cstr(Year(Now())*10000+month(now())*100+day(now())) +"]"
SELECT
{
[Measures].[Sales_last_15_days]
} ON COLUMNS
FROM
[CubeName]
WHERE
STRTOMEMBER([Measures].[CurrDate])
[/code]
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.
