SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

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