Desktop

[jeffreyweir]

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. 

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.

[OwenAuger · 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

[Phil_Seamark]

Hi @jeffreyweir,

So what columns does your dataset already have, and which columns would you like a calcualted column/measure for?

And do you have a txt version of the data to make it easier to import, rather than a photo to help speed up the suggestions

[jeffreyweir]

Whoops, sorry Phil...forgot to upload that file. Will upload to onedrive and post link as soon as I'm able (can't do it from work) but meanwhile have emailed you a copy in spreadsheet form.

In regards to the rest of your question, I'm not quite sure I follow. Using the sample data, the calculated column needs to match the sample results in the 'Cumulative Total' column, and it needs to reference the Emp_ID column, the Workday column, and the Score column.

[jeffreyweir]

Here's a link to the sample file:
https://1drv.ms/x/s!Ah_zTnaUo4Dzjnt6KWy8ZxWAEfPC

[jeffreyweir]

To clarify, the tricky part isn't in creating a cumulative total, but rather adjusting that cumulative total downwards by 50% for every non work day. I seriously don't think DAX can do this. But I'd love to be proven wrong.

[OwenAuger · 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

[Sean]

@OwenAuger  Great Job again!

@Phil_Seamark  I was not responding to you because I was looking into this...

Now have to process Owen's solution

EDIT: @MarcelBeug ??? we are all waiting for the M solution

[jeffreyweir]

Far out! When I half explained this issue to Phil the other day and he notchelently said "yeah, DAX can do that", well...to quote The Castle, I thought he was "dreamin".

This solution is going "straight to the Pool Room."

Now to step through it. I'm an Excel Formulas and VBA dude, so DAX is still pretty new to me. This has doubled my interest in DAX. Many, many thanks.

[Phil_Seamark]

I had a look at an M approach earlier using List.Accumulate but it didn't provide enough granularity over the iterations to change the operation.  I didn't look into creating a function.  I definitey think @MarcelBeug will be able to come up with something pretty cool.

[MarcelBeug]

@Sean, @Phil_Seamark

Would this List.Generate based M Solution be cool enough?

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Typed1 = Table.TransformColumnTypes(Source,{{"Emp_ID", Int64.Type}, {"Date", type date}, {"Cumulative Days", Int64.Type}, {"Workday", type logical}, {"Score", Int64.Type}, {"Cumulative Total", type number}}),
    Removed = Table.RemoveColumns(Typed1,{"Cumulative Total"}),
    Cumulation = List.Generate(() => [Index = 1,
                                     Employee = Removed[Emp_ID]{0},
                                     Total = if Removed[Workday]{0} then Removed[Score]{0} else 0],

                            each [Index] <= Table.RowCount(Removed),

                            each [Index = [Index] + 1,
                                  Employee = Removed[Emp_ID]{[Index]},
                                  Total = if Employee <> [Employee]
                                          then if Removed[Workday]{[Index]} 
                                               then Removed[Score]{[Index]}
                                               else 0
                                          else if Removed[Workday]{[Index]}
                                               then [Total] + Removed[Score]{[Index]}
                                               else [Total] * 0.5],

                            each [Total]),

    Records = Table.ToRecords(Removed),
    CombinedTable = Table.FromColumns({Records,Cumulation},{"Records","Cumulative Total"}),
    ExpandedRecords = Table.ExpandRecordColumn(CombinedTable, "Records", {"Emp_ID", "Date", "Cumulative Days", "Workday", "Score"}, {"Emp_ID", "Date", "Cumulative Days", "Workday", "Score"}),
    Typed2 = Table.TransformColumnTypes(ExpandedRecords,{{"Cumulative Total", type number}})
in
    Typed2