MDX-How do I calculate sales for 12 Month to date?

Q: How do I calculate sales for 12 Month to date in MDX?

A: We have to assume that your date dimension last member represents last month. Then to get last 12 month we can use Lag function. Example:

SELECT [Measures].[Order Count] ON 0
, ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12)
: ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]) ON 1
FROM [Adventure Works]

Result:

	Order Count
August 2003	1,759
September 2003	1,783
October 2003	1,779
November 2003	1,888
December 2003	2,272
January 2004	1,944
February 2004	2,030
March 2004	2,108
April 2004	2,127
May 2004	2,385
June 2004	2,374
July 2004	976
August 2004	(null)

Now to get sum of sales query could be :

WITH MEMBER [Measures].[Last 12 Mth Order Count] AS
SUM(
ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12)
: ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods])
, [Measures].[Order Count]
)
SELECT [Measures].[Last 12 Mth Order Count] ON 0
FROM [Adventure Works]

Result

Last 12 Mth Order Count
23,425