cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

DAX formula to calculate cumulative running total across two columns that halves on non workdays

Okay, so this isn't strictly a PowerBI problem, but rather a DAX problem that I'm trying to solve in PowerPivot./Excel, although we will be migrating the workbook to PowerBI in due course.

 

So here's my problem:

I have some data (90,000 rows) I'm trying to use to calculate a cumulative "fatigue score" for folk working shifts...currently 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 halve the cumulative sum for each line that Workday is FALSE. . My desired output is in the 'Cumulative Total' column far right, and I've used grey 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. 

 

Capture.PNG

 

This is very similar to a question I asked previously at StackOverflow, with the difference that I need the cumulative total to halve every time the Workday value is FALSE. Alejondro came up with a solution to that problem that looked like this:

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

 

...but I need to amend this to halve the cumulative total each time the Workday value is FALSE.

 

While making these kinds of adjustments to cumulative totals is very simple in Excel, I'm not sure this is possible in DAX.  Currently we are in fact using PowerPivot to serve up the data in Excel, and then using Excel to create the cumulative logic, and then pulling the cumulative totals back into PowerPivot via a linked table, which works but is clunky.

 

 

1 ACCEPTED SOLUTION

Hi @jeffreyweir

 

@Phil_Seamark alerted me to this thread.

 

Yes, your Cumulative Total can be done in a DAX calculated column. (You could also consider Power Query).

 

Sample pbix here to illustrate the DAX.

 

A version of the column is:

Cumulative Total (new DAX) = 
VAR OuterDate = Shifts[Date]
RETURN
    SUMX (
        CALCULATETABLE (
            Shifts,
            ALLEXCEPT ( Shifts, Shifts[Emp_ID] ),
            Shifts[Date] <= OuterDate
        ),
        VAR InnerDate = Shifts[Date]
        RETURN
            POWER (
                0.5,
                CALCULATE (
                    COUNTROWS ( Shifts ),
                    ALLEXCEPT ( Shifts, Shifts[Emp_ID] ),
                    Shifts[Date] <= OuterDate,
                    Shifts[Date] >= InnerDate,
                    NOT ( Shifts[Workday] )
                )
            )
                * Shifts[Score]
    )

Out of interest, a version that works in "old DAX" without variables is:

Cumulative Total (old DAX) = 
SUMX (
    CALCULATETABLE (
        Shifts,
        ALLEXCEPT ( Shifts, Shifts[Emp_ID] ),
        Shifts[Date] <= EARLIER ( Shifts[Date] )
    ),
    POWER (
        0.5,
        CALCULATE (
            COUNTROWS ( Shifts ),
            ALLEXCEPT ( Shifts, Shifts[Emp_ID] ),
            Shifts[Date] <= EARLIER ( Shifts[Date], 2 ),
            Shifts[Date] >= EARLIER ( Shifts[Date] ),
            NOT ( Shifts[Workday] )
        )
    )
        * Shifts[Score]
)

The way I've replicated the recursive calculation is to

  1. Grab the rows of shifts for the current employee up to the current date.
  2. For each of those rows, count the number of non-work days from that row's date to the current date.
  3. Calculate 0.5^(row count from step 2) and multiply by each row's Score, then sum.

The reason this works is that if you imagine calculating the Cumulative Total for each row in turn, every time you hit another non-work day, each previous row gets multiplied by another factor of 0.5.

 

 

Cheers,

Owen

 


Owen Auger

Did I answer your question? Mark my post as a solution!

Connect on Twitter
Connect on LinkedIn

View solution in original post

25 REPLIES 25

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors