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

Written by Thomas Ivarsson. Posted in MDX FAQ

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:

MyYTD MyMonthCount Internet Sales Amount MyYTDAVG
July 2001 $473,388.16 1 $473,388.16 $473,388.16
July 2002 $4,306,075.74 7 $500,365.16 $615,153.68
July 2003 $3,924,170.20 7 $886,668.84 $560,595.74
July 2004 $9,770,899.74 7 $50,840.63 $1,395,842.82

Done

Tags: mdx, faq