Thursday, April 8, 2010

Finding a Last 52 Weeks based on current day in MDX

WITH
SET
How to find the last 52 weeks data based on current week as the end week in MDX?

There are two ways to achieve this:

First using Lag() function and such that you can define the range as 51 weeks prior to the current week. Below is the sample MDX query to achieve this.


Last52Weeks AS
{STRTOMEMBER("[Date].[Calendar Week of Year].&[25]", CONSTRAINED).Lag(51):STRTOMEMBER("[Date].[Calendar Week of Year].&[25]", CONSTRAINED)}
SELECT {
[Measures].[Internet Sales Amount]
} ON 0,
NON EMPTY {
Last52Weeks
} ON 1
FROM [Adventure Works]


Second way of achieving this using LastPeriods() function.

SELECT Tail([Time].[Fiscal YWD].[WEEK].MEMBERS,1) ON 1
, NULL ON 0 FROM PROFITBASE
WHERE
LASTPERIODS(365,STRTOMEMBER(@CurrentDay,CONSTRAINED))

No comments: