Monday, July 28, 2014

PREVMEMBER and NEXTMEMBER

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:
image
(Yep, we need to build in some NULL handling logic)
Have Fun And Till Next Time

No comments:

Post a Comment