The LAG and LEAD functionality allow to jump back or forward a number of step on the same hierarchy level.
LAG(1) and LEAD(1) is the same as PrevMember and Next Member.
WITH
MEMBER [Measures].[Previous Sales Amount -1] AS
SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.LAG(1)), FORMAT_STRING="Currency"
MEMBER [Measures].[Previous Sales Amount -2] AS
SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.LAG(2)), FORMAT_STRING="Currency"
MEMBER [Measures].[Previous Sales Amount -4] AS
SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.LAG(4)), FORMAT_STRING="Currency"
MEMBER [Measures].[Next Sales Amount +1] AS
SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.LEAD(1)), FORMAT_STRING="Currency"
MEMBER [Measures].[Next Sales Amount +2] AS
SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.LEAD(2)), FORMAT_STRING="Currency"
MEMBER [Measures].[Next Sales Amount +4] AS
SUM([Measures].[Sales Amount],[Date].[Calendar].CURRENTMEMBER.LEAD(4)), FORMAT_STRING="Currency"
SELECT
{
[Measures].[Sales Amount]
, [Measures].[Previous Sales Amount -1]
, [Measures].[Previous Sales Amount -2]
, [Measures].[Previous Sales Amount -4]
, [Measures].[Next Sales Amount +1]
, [Measures].[Next Sales Amount +2]
, [Measures].[Next Sales Amount +4]
} ON COLUMNS,
[Date].[Calendar Year].MEMBERS ON ROWS
FROM [Adventure Works]
Will give use:
Till Next Time And Have Fun.
No comments:
Post a Comment