MDX Query with running total across crossjoined dimensions

Question

I have a cube with the following three dimensions: Date, Time and Shift. I have a measure called [Pieces Succeeded], and I want a running total of the [Pieces Succeeded] by hour for a Shift. A Shift can span more than one day, so in the following query, I do a crossjoin of the Date and Time dimensions. ``` with member [Measures].[Pieces Succeeded Running Total] as sum([Time].[Hierarchy].[Hour].FirstMember:[Time].[Hour].CurrentMember, [Measures].[Pieces Succeeded]) select { [Measures].[Pieces Succeeded], [Measures].[Pieces Succeeded Running Total] } on columns, nonempty(crossjoin([Date].[Month Hierarchy].[Day].Members, [Time].[Hierarchy].[Hour].Members)) on rows from [OEE] where [Shift].[Month Hierarchy].[Shift].&[501] ``` Which gives the following results: ``` Date Hour Pieces Succeeded Pieces Succeeded Running Total 03 Apr 2011 22 6393 6393 03 Apr 2011 23 6424 12817 04 Apr 2011 00 3816 3816 04 Apr 2011 01 5510 9326 04 Apr 2011 02 2090 11416 04 Apr 2011 03 7489 18905 04 Apr 2011 04 73…

Accepted Answer

I spent all day on this, and finally figured it out. I thought it might be valuable for someone else, so here's the solution:

```
with

set DateHours as
    nonempty(crossjoin([Date].[Month Hierarchy].[Day].Members, [Time].[Hierarchy].[Hour].Members), [Measures].[Pieces Succeeded])

member [Measures].[Rank] as
    rank(([Date].[Month Hierarchy].CurrentMember, [Time].[Hierarchy].CurrentMember ), DateHours)

member [Measures].[Running Pieces Succeeded] as 
    iif([Measures].[Rank] = 1, [Measures].[Pieces Succeeded], sum(head(DateHours, [Measures].[rank]), [Measures].[Pieces Succeeded]))

select
    { [Measures].[Pieces Succeeded], [Measures].[Running Pieces Succeeded] } on columns,
    non empty { DateHours } on rows

from
    [OEE]

where
    [Shift].[Month Hierarchy].[Shift].&[501]
```

MDX Query with running total across crossjoined dimensions

Solution

Problem