Creating an SSAS MDX calculations for daily average sales is a pretty common requirement I’ve come across as a consultant for Pragmatic Works and as the instructor for Pragmatic Works Introduction to MDX class. Because of this and the fact that many people often come to me with their MDX challenges, I’ve decided to start a series of blog posts covering these challenges and experiences both as a reference for myself and other and also as a way to give back to the community. These examples will be done using the Adventure Works cube unless otherwise stated. Some of these will be simple and some will be more complicated but I hope you will enjoy learning a little bit about MDX as much I do so lets begin!

There are two basic parts to creating this calculation. The first step is we need to create a measure to count the days of whatever period we happen to be browsing. To do this we can use the Count function and the Descendants function.

with member [Measures].[Day Count] as 

    Count(
        Descendants(
            [Date].[Calendar].currentmember,
            [Date].[Calendar].[Date]
            )
        )

Select {[Measures].[Day Count]} on 0,

NON EMPTY
[Date].[Calendar].[Month].members on 1

From [Adventure Works]

Now that the calculation for the day count is working, all we have to do is create an additional calculation to divide the Internet Sales Amount measure by the Day Count measure.

with member [Measures].[Day Count] as 

    Count(
        Descendants(
            [Date].[Calendar].currentmember,
            [Date].[Calendar].[Date]
            )
        )

member [Measures].[Average Daily Sales] as

[Measures].[Internet Sales Amount]/[Measures].[Day Count]

Select {[Measures].[Day Count],
    [Measures].[Average Daily Sales]} on 0,

NON EMPTY
[Date].[Calendar].[Month].members on 1

From [Adventure Works]

And there we have it. You may be wondering about why I didn’t use the Average function. The Average function only calculates the average over non-empty cells but for our purposes we need to consider days when a sale did not occur so the Average function won’t work in this situation.

More MDX Resources

If you’re looking for some other helpful resources related to MDX here’s some suggestions:

SQLDusty.com/category/mdx/ – Duh! 😉
MDXpert.com – Great resource for information on most of the MDX functions.
Chris Webb’s blog – Chris Webb has a lot of great MDX info on his site. Definitely worth the visit.

Got Feedback?

If you have any feedback or questions regarding this, please leave a comment down below or send me a message on Twitter!