Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
jeffreyweir
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!
Blog
Twitter
LinkedIn

View solution in original post

25 REPLIES 25

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.