DAX running total based on 3 columns, one of which is a repeating integer running total

Question

Very new to DAX/PowerPivot, and faced with devilishly tricky question on day one.

I have some data (90,000 rows) I'm trying to use to calculate a cumulative fatigue score for folk working shifts(using PowerPivot/Excel 2016). As per the below screenshot, the dataset is shift data for multiple employees, that has a cumulative count of days worked vs. days off that resets back to 1 whenever they switch from one state to the other, and a 'Score' column that in my production data contains a measure of how fatigued they are.

I would like to cumulatively sum that fatigue score, and reset it whenever they move between the 'Days worked' and 'Days off' states. My desired output is in the 'Desired' column far right, and I've used green highlighting to show days worked vs. days off as well as put a bold border around separate Emp_ID blocks to help demonstrate the data.

There is some similarity between my question and the SO post at DAX running total (or count) across 2 groups except that one of my columns (i.e. the Cumulative Days one) is in a repeating sequence from 1 to x. And Javier Guillén's post would probably make a good starting point if I'd had a couple of months of DAX under my belt, rather than the couple of hours I've gained today.

I can barely begin to conceptualize what the DAX would need to look like, given I'm a DAX newbie (my background is VBA, SQL, and Excel formulas). But lest someone berate me for not even providing a starting point, I tried to tweak the following DAX without really having a clue what I was doing:

 Cumulative:=CALCULATE( 
SUM( Shifts[Score] ) , 
FILTER(Shifts,Shifts[Cumulative Days] <= VALUES(Shifts[Cumulative Days] )) , 
ALLEXCEPT( shifts, Shifts[Workday],Shifts[EMP_ID] ) )

Now I'll be the first to admit that this code is DAX equivelant of the Infinite Monkey Theorem. And alas, I have no bananas today, and my only hope is that someone finds this problem suitably a-peeling.

Answer 1

The problem with this table is there is no way to determine when stop summing while performing the cumulative total.

I think one way to achive it could be calculating the next first date where continuous workday status changes.

For example the workday status in the first three rows for EMP_ID 70073 are the same, until the fourth row, date 04-May which is the date the workday status changes. My idea is to create a calculated column that find the status change date for each workday serie. That column lets us implement the cumulative sum.

Below is the expression for the calculated column I named Helper.

Helper =
IF (
    ISBLANK (
        CALCULATE (
            MIN ( [Date] ),
            FILTER (
                'Shifts',
                'Shifts'[EMP_ID] = EARLIER ( 'Shifts'[EMP_ID] )
                    && 'Shifts'[Workday] <> EARLIER ( 'Shifts'[Workday] )
                    && [Date] > EARLIER ( 'Shifts'[Date] )
            )
        )
    ),
    CALCULATE (
        MAX ( [Date] ),
        FILTER (
            Shifts,
            Shifts[Date] >= EARLIER ( Shifts[Date] )
                && Shifts[EMP_ID] = EARLIER ( Shifts[EMP_ID] )
        )
    )
        + 1,
    CALCULATE (
        MIN ( [Date] ),
        FILTER (
            'Shifts',
            'Shifts'[EMP_ID] = EARLIER ( 'Shifts'[EMP_ID] )
                && 'Shifts'[Workday] <> EARLIER ( 'Shifts'[Workday] )
                && [Date] > EARLIER ( 'Shifts'[Date] )
        )
    )
)

In short, the expression says if the date calculation for the current workday series change returns a blank use the last date for that EMP_ID ading one date.

Note there is no way to calculate the change date for the last workday serie, in this case 08-May rows, so if the the calculation returns blank it means it is being evaluated in the last serie then my expression should return the max date for that EMP_ID adding one day.

Once the calculated column is in the table you can use the following expression to create a measure for the cumulative value:

Cumulative Score =
CALCULATE (
    SUM ( 'Shifts'[Score] ),
    FILTER ( ALL ( 'Shifts'[Helper] ), [Helper] = MAX ( [Helper] ) ),
    FILTER ( ALL ( 'Shifts'[Date] ), [Date] <= MAX ( [Date] ) )
)

In a table in Power BI (I have no access to PowerPivot at least eight hours) the result is this:

I think there is an easier solution, my first thought was using a variable, but that is only supported in DAX 2015, it is quite possible you are not using Excel 2016.

UPDATE: Leaving only one filter in the measure calculation. FILTER are iterators through the entire table, so using only one filter and logic operators could be more performant.

Cumulative Score =
CALCULATE (
    SUM ( 'Shifts'[Score] ),
    FILTER (
        ALL ( 'Shifts'[Helper], Shifts[Date] ),
        [Helper] = MAX ( [Helper] )
            && [Date] <= MAX ( [Date] )
    )
)

UPDATE 2: Solution for pivot tables (matrix), since previous expression worked only for a tabular visualization. Also measure expression was optimized to implement only one filter.

This should be the final expression for pivot table:

Cumulative Score =
CALCULATE (
    SUM ( 'Shifts'[Score] ),
    FILTER (
        ALLSELECTED ( Shifts ),
        [Helper] = MAX ( [Helper] )
            && [EMP_ID] = MAX ( Shifts[EMP_ID] )
            && [Date] <= MAX ( Shifts[Date] )
    )
)

Note: If you want to ignore filters use ALL instead of ALLSELECTED.

Results in Power BI Matrix:

Results in PowerPivot Pivot Table:

Let me know if this helps.