Analysis Services (SSAS): Calculating running totals for a given period using MDX

Calculating running totals against a numeric is not an easy task with both relational databases using T-SQL and multidimensional databases using MDX. In a way, calculation on relational databases is little bit easier using new window functions than using multi-dimensional queries. But it does not mean that the calculation using MDX is much more complex and difficult to write. However it depends on the familiarity. Comparatively we do not write MDX statements as much as we write T-SQLs. That is the reason for tagging “complex” on MDX, that is the reason for spending much time even on a simple query than the time spend with T-SQL for similar implementations.

I had to write a similar query today for calculating running totals mixing with YTD and previous year values. As usual I searched for best practices, there were many posts on this based on either calendar year or financial year but could not find a specific one for calculating running totals for a given period. Wrote it, and thought to share it. Here is the MDX, it is written on AdventureWorksDW. You may find it interesting and helpful.

1. WITH MEMBER [Measures].[Running Total]
2. AS
3. (
4.     AGGREGATE([Date].[Calendar].[Month].&[2006]&[10]
5.             :[Date].[Calendar].CurrentMember
6.             , [Measures].[Sales Amount])
7. )
8. SELECT
9.     {[Measures].[Sales Amount]
10.     , [Measures].[Running Total]} ON 0
11.     , [Date].[Calendar].[Month].Members ON 1
12. FROM (SELECT {[Date].[Calendar].[Month].&[2006]&[10]
13.             :[Date].[Calendar].[Month].&[2007]&[11]} ON 0
14.         FROM [Adventure Works])