|MDX-How do you calculate monthly average of one year, optionally including empty months?|
|Written by Vidas Matelis|
|Saturday, 14 April 2007 14:39|
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:
We included filter for Product so numbers would be easier to understand.
Total sum of these values is 357.
To get Average value we can use following query:
Result of this query:
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:
And now results will be:
- How to install Adventure Works SQL DW and Analysis Services 2005/2008 sample database and project
- MDX-How can I get Last (Previous) Year to Date (YTD) values?
- What TCP port SQL Server Analysis Services 2005 uses
- MDX-How do you calculate monthly average of one year, optionally including empty months?
- MDX-How do I calculate sales for 12 Month to date?