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

Q: How do you calculate monthly average of a year, optionally including empty months?

A: Lets say we would like to calculate average of Internet Order count from Adventure works database for year 2004. Lets first check what data we have in database:

SELECT {[Measures].[Internet Order Count]} ON 0
, Descendants([Date].[Calendar].[Calendar Year].&[2004], [Date].[Calendar].[Month]) ON 1
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])

We included filter for Product so numbers would be easier to understand.
Results of above query:

Internet Order Count
January 2004 51
February 2004 52
March 2004 53
April 2004 60
May 2004 57
June 2004 49
July 2004 35
August 2004 (null)

Total sum of these values is 357.
As we counted for 8 months, average value would be 357/8= 44.625
If we exclude August with value NULL, average value would be 357/7=51

To get Average value we can use following query:

WITH MEMBER [Measures].[AvgVal] AS
Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month])
, [Measures].[Internet Order Count]
)
SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])

Result of this query:

AvgVal
51

As you see MDX function AVG did not include August month into calculation as for that month measure value is NULL.

If we want to include NULL value, we have to replace value NULL for our measure with value 0. This could be done using CoalesceEmpty function. So our query will be:

WITH MEMBER [Measures].[AvgVal] AS
Avg( Descendants([Date].[Calendar].[Calendar Year].&[2004]
, [Date].[Calendar].[Month])
, CoalesceEmpty([Measures].[Internet Order Count], 0)
)
SELECT {[Measures].[AvgVal]} ON 0
FROM [Adventure Works]
WHERE ([Product].[Product Model Lines].[Model Name].&[Classic Vest])

And now results will be:

AvgVal
44.625