Cumulative Sum as Measure with MDX

Calculation in MDX is fast and cheaper that T-SQL because of the cube structure. one thing that is needed in business is the cumulative sum dynamically over periods in different aspects (e.g. Inventory Trend in Retail). there is an easy way to create a calculation member in your project or use it in the MDX query as well.

We can build this measure in two ways. First, when we are writing a select query we could create that on the fly like this: (This query executed on restored ABF backup of adventure works multidimensional database)

WITH MEMBER [Measures].[CumulativeSum] AS
SUM(
    NULL:[Date].[Date].CurrentMember, 
    [Measures].[Internet Sales Amount]
)

SELECT 
    {
        [Measures].[Internet Sales Amount],
        [Measures].[CumulativeSum] 
    } ON COLUMNS,
    NONEMPTY(
        {
            [Date].[Date].[Date]
        }) ON ROWS
FROM [Adventure Works]

WITH MEMBER creates a member for the select query which calculates summation of Internet Sales Amount of Adventure Works around Date dimension set from Null into Current Member of each member of the dimension.

The second way is to create a calculation member in our SSAS multidimensional project. In a project that is created we use Fact Internet Sale and Dim Date, then build a cube and its dimensions based on existing tables and relation. Afterward, we go to the calculation tab in the cube and right-click on calculate area and choose New Calculated Member.

In the next step, we just need to write a cumulative sum formula for new dimension and group of measures into expression. and set parent hierarchy as measures.

Now when we browse the cube or get cube as the data source in excel or anywhere else, new cumulative sum measure appears in measure group and it gets the result as below: