How do I create a Rolling 12 Months Accumulated Sum that can show a trend without seasonal variations
How do I create a Rolling 12 Months Accumulated Sum (InternetSalesAmtR12Acc) that can show a trend without seasonal variations?
Here is query example
WITH MEMBER [Measures].[InternetSalesAmtYTD] AS SUM(YTD([Date].[Calendar].CurrentMember),[Measures].[Internet Sales Amount]), Format_String = "### ### ###"
MEMBER [Measures].[InternetSalesAmtPPYTD] AS SUM(YTD(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember)),
[Measures].[Internet Sales Amount]), Format_String = "### ### ###"MEMBER [Measures].[InternetSalesAmtPY] AS SUM(Ancestor(ParallelPeriod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].CurrentMember),[Date].[Calendar].[Calendar Year]),
[Measures].[Internet Sales Amount]),Format_String = "### ### ###"MEMBER [Measures].[InternetSalesAmtR12Acc] AS ([Measures].[InternetSalesAmtYTD]+[Measures].[InternetSalesAmtPY] )- [Measures].[InternetSalesAmtPPYTD]
Select {[Measures].[Internet Sales Amount], Measures.[InternetSalesAmtYTD], [Measures].[InternetSalesAmtPPYTD],[Measures].[InternetSalesAmtR12Acc]} On 0,
[Date].[Calendar].[Month].Members On 1
From [Adventure Works]
Where ([Date].[Calendar Year].&[2004]);
Result will be:
Internet Sales Amount InternetSalesAmtYTD InternetSalesAmtPPYTD InternetSalesAmtR12Acc Jan-04 $1,340,244.95 1 340 245 438 865 10 692 440 Feb-04 $1,462,479.83 2 802 725 927 956 11 665 830 Mar-04 $1,480,905.18 4 283 630 1 413 530 12 661 160 Apr-04 $1,608,750.53 5 892 380 1 919 930 13 763 511 May-04 $1,878,317.51 7 770 698 2 482 702 15 079 056 Jun-04 $1,949,361.11 9 720 059 3 037 501 16 473 618 Jul-04 $50,840.63 9 770 900 3 924 170 15 637 790 Aug-04 (null) 9 770 900 4 771 584 14 790 376