The PREVMEMBER functionality will give you the previous member on the same hierarchy level. 1-JAN-2011 ==> 31-DEC-2010, 2-JAN-2011 ==> 1-JAN-2011, CY 2011 ==> CY 2010.
The NEXTMEMBER functionality will give you the next member on the same hierarchy level. 1-JAN-2011 ==> 2-JAN-2011, 31-DEC-2011 ==> 1-JAN-2012, CY 2011 ==> CY2012
We can use this calculate difference between periods.
WITH
MEMBER [Measures].[Previous Sales Amount] AS
SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.PREVMEMBER), FORMAT_STRING="Currency"
MEMBER [Measures].[Next Sales Amount] AS
SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.NEXTMEMBER), FORMAT_STRING="Currency"
MEMBER [Measures].[Next Sales Amount Delta] AS
SUM([Measures].[Sales Amount]) - SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.NEXTMEMBER), FORMAT_STRING="Currency"
MEMBER [Measures].[Previous Sales Amount Delta] AS
SUM([Measures].[Sales Amount]) - SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.PREVMEMBER), FORMAT_STRING="Currency"
SELECT
{
[Measures].[Previous Sales Amount]
, [Measures].[Sales Amount]
, [Measures].[Next Sales Amount]
, [Measures].[Next Sales Amount Delta]
} ON COLUMNS,
[Date].[Calendar Year].MEMBERS ON ROWS
FROM [Adventure Works]
This will get us:
(Yep, we need to build in some NULL handling logic)
Have Fun And Till Next Time
No comments:
Post a Comment