SSAS FAQ MDX FAQ How to calculate YTD monthly average and compare it over several years for the same selected month
How to calculate YTD monthly average and compare it over several years for the same selected month
User Rating: / 2
PoorBest 
Written by Thomas Ivarsson   
Monday, 14 July 2008 02:51

Q: How to calculate YTD monthly average and compare it over several years for the same selected month?

A: MDX Query:

WITH MEMBER Measures.MyYTD AS SUM(YTD([Date].[Calendar]),[Measures].[Internet Sales Amount])

MEMBER Measures.MyMonthCount AS SUM(YTD([Date].[Calendar]),(COUNT([Date].[Month of Year])))

MEMBER Measures.MyYTDAVG AS Measures.MyYTD /  Measures.MyMonthCount

SELECT  {Measures.MyYTD, Measures.MyMonthCount,[Measures].[Internet Sales Amount],Measures.MyYTDAVG} On 0,
 [Date].[Calendar].[Month] On 1
FROM [Adventure Works]
WHERE ([Date].[Month of Year].&[7])

Result:

 MyYTDMyMonthCountInternet Sales Amount MyYTDAVG
July 2001$473,388.161$473,388.16$473,388.16
July 2002$4,306,075.747$500,365.16$615,153.68
July 2003$3,924,170.207$886,668.84$560,595.74
July 2004$9,770,899.747$50,840.63$1,395,842.82

 

Done

Next >