MDX Cumulative Total with Period Prompt Selection

[eliza_jane]

We have a single select Period tree prompt. The chart should show the last 10 periods upto and including the chosen period. A Period could be Year, Quarter or Month. The total displayed needs to be a cumulative total.

So if the user selects year 2013. The chart should show 2010 to 2013 periods with cumulative total ranging from 2010 to 2013.

I have been trying to use Periodstodate function but with no luck.

This is against DMR, DQM.

[CognosPaul]

If the user selects 2013, shouldn't the start be 2003? Or does the data only start in 2010?

Let's take September 2013. If I understand the requirement, the chart should start at December, 2012 and show each month until September 2013. For each month there should be two measures displayed. The actual value for that month, and the cumulative total for the displayed months.

The categories of the chart is easy. We'll call that last10Periods:
lastPeriods(10,#prompt('Period','mun','','','[Cube].[Time Dim].[Time Hierarchy]')#)

Showing the cumulative total for only displayed months makes it a bit more difficult. The standard way to do a running total in OLAP is:
total([Measure] within set periodsToDate([Cube].[Time Dim].[Time Hierarchy].[All Level],currentMember([Cube].[Time Dim].[Time Hierarchy])))

Instead, let's try tweaking that a bit.

Code Select Expand


total(
  [Measure]
  within set
  except(
    [last10Periods]
    , except(
      [last10Periods]
      , lastPeriods(
        10
        , currentMember([Cube].[Time Dim].[Time Hierarchy])
      )
    )
  )
)

First it generates a set of the last ten periods from the current month in the chart, so for December it would be March - December. It then runs the except on the last10Periods set, removing all matching members. So, on the December node it would January - September. Finally it does an except on last10Periods again removing the set. So the December node would return December, February would return December;February, and so on.

This solution works very well in my SSAS cube, and the generated MDX looks very reasonable. Unfortunately I don't have a DMR environment to test this in, so let me know how it goes.

[eliza_jane]

Hi Paul, Thanks a ton for the solution...You continue to amaze me.