Cumulative Total and Net Incremental Cost

[cathoms]

Hello greatest support community in the known web!

Leadership wants to see change in incremental cost over time. In this case, incremental cost is the difference in the hourly rate of travelers and regular staff. Travelers are much more expensive. This Cumulative Total thread was very helpful in getting cumulative incremental cost for contract starts but I am struggling to write a measure that will give me cumulative cost based on contract end date.

Data Model Notes

I have a DateDim table and TravelerFact table. Inactive relationships from DateDim.DateValue to TravelerFact.Start Date and TravelerFact.EndDate. Start date is the date a contract starts and end date is the date the contract ends.

Sample Traveler Data

TravelerFact_Sample (Google Sheet). Note that not on the sheet is a calculated column I created for "Start Year-Week", based on Start Date.

Measures:

This one works as expected:

Contract Starts Incremental Cost = 
CALCULATE (
    SUM ( TravelerFact[HourlyRateDiff] ),
    FILTER (
        ALL ( TravelerFact ),
        TravelerFact[Start Date] <= MAX ( TravelerFact[Start Date] )
    ),
    USERELATIONSHIP ( TravelerFact[Start Date], DateDim[DateValue] )
)

Then I tried the same for contract end date to see how much cost is "rolling off" each week as contracts end.

Contract Ends Incremental Cost = 
CALCULATE (
    SUM ( TravelerFact[HourlyRateDiff] ),
    FILTER (
        ALL ( TravelerFact ),
        TravelerFact[End Date] <= MIN ( TravelerFact[End Date] )
    ),
    USERELATIONSHIP ( TravelerFact[End Date], DateDim[DateValue] )
)

I end up with tables like the below with unexpected values in Contract Ends Incremental Cost. In the first table I would expect rows to be blank until about week 27 because the earliest contract end date is 1 July 2022. In the second I would expect cumulative values starting with week 27 and on. How can I calulate the cumulative sum of HourlyRateDiff based on the End Date?

[cathoms · Accepted solution]

So, after a hint from a colleague including a couple videos on calculating total number of staff over time, and calculating staff turnover, I figured out the issue and created the following measures. Note that this solution requires having two inactive relationships: between a Date dimension table and both Start Date and End Date in the fact table.

Contract Starts Incremental Cost = 
CALCULATE(
    SUM( TravelerFact[HourlyRateDiff] ),
    FILTER(
        VALUES( TravelerFact[Start Date] ),
        TravelerFact[Start Date] <= MAX ( DateDim[DateValue] )
    ),
    FILTER( VALUES(TravelerFact[End Date]), 
    TravelerFact[End Date] >= MIN( DateDim[DateValue])
    )
)


Contract Ends Incremental Cost =
CALCULATE (
    SUM ( TravelerFact[HourlyRateDiff] ),
    FILTER (
        VALUES ( TravelerFact[End Date] ),
        TravelerFact[End Date] <= MIN ( DateDim[DateValue] )
    ),
    TravelerFact[End Date] <> BLANK () --not really needed as there are no blanks in this dataset
)

Now it is doing what I expected:

[v-yanjiang-msft]

Hi @cathoms ，

Have you tried to create a year-month column in DateDim table, if not, please have a try.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cathoms]

Not sure what a year-month column would do for me. I need to show this by week...

[cathoms]

Quick addendum - the Contract Ends Incremental Cost works on my table if I use End Year-Week from my TravelerFact table (see below). The issue is, I need some way to correctly display both starts and ends incremental cost week to week from the beginning of the year on the same visual AND to calculate net weekly incremental cost, i.e. net cost starts - net cost ends.