Search code examples
excelpowerbipowerpivotdaxmeasure

Running total of a Measure

i'm looking to create a running total of a MEASURE (here name Open vs Closed). I have all my colums correct except the last one "Total Open". Anybody knows a measure to have this as a running total of the Open vs Closed column ?

WeekIndex |Open Incidents | Closed Incidents | Open vs Closed | Total Open
1         | 5             | 0                | +5             | 5
2         | 4             | 5                | -1             | 4
3         | 2             | 0                | +2             | 6
4         | 3             | 3                | +0             | 6
5         | 10            | 12               | -2             | 4
Solution

  • A calculated column using the EARLIER function can do in this case:

    Total Open = 
CALCULATE(
    SUM('Table'[Open vs Closed]),
    FILTER(
        'Table',
        'Table'[WeekIndex] <= EARLIER('Table'[WeekIndex])
    )
)

    Results:

    results

    Updated:

    The following measure should work in this case. The ALL function is needed to ignore the row level context when it's put in the table:

    Total Open Measure = 
CALCULATE(
    [Open vs Closed Measure],
    FILTER(
        ALL('Table'),
        'Table'[WeekIndex] <= MAX('Table'[WeekIndex])
    )
)

    results2

    Second update:

    Given the weird case that all columns are measures:

    Total Open Measure = 
VAR CurrentIndex = [Index]
RETURN
CALCULATE(
    [Open vs Closed Measure],
    FILTER(
        ALL('Table'),
        [Index] <= CurrentIndex
    )
)

    results3

    Third Update:

    I can only come up with a way using the MonthIndex. I think the reason of that extra 6 is due to the way you calculate Incidents Closed. Anyways I fix it by adding 'Calendar'[MonthIndex] >= 1 to the filter:

    Total Open Measure = 
CALCULATE(
    [Open vs Closed],
    FILTER(
        ALL('Calendar'),
        'Calendar'[MonthIndex] >= 1 &&
        'Calendar'[MonthIndex] <= MAX('Calendar'[MonthIndex])
    )
)

    To filter out the rows without incidents I added a visual level filter on the Index measure:

    results4