Calculating the split of durations between two days

[willpage]

Hello,

Looking for a bit of best practice guidance here.

I have a table for timesheets. The timesheets belong to truck drivers and often span multiple days. Frequently two days and sometimes more than 2 days.

I perform a week to date calculation for each driver to track their hours worked in the current week from 00:00 Monday morning. This I have suceeded in, but one of the requirements is that if the driver clocked on Sunday and drove overnight, the portion of the shift after midnight Sunday mustt be included in the WTD caculation.

The data looks like this. In this example the timesheets don't actually span weeks. I don't have enough data in the db yet to find one that does, but I need to work on the basis it will.

So, I added a new boolean column in Power Query called "Spans Weeks" which is simply if the start Day of the week > Finish day of the week (Monday being 0) then true.

I don't really mind whether I do this in PQ or DAX or however, I'm looking for what best practice is really.

I need to split such a timesheet where Spans Weeks = true such that all the time that occurs prior to midnight Sunday is ignored by my WTD calc and all the time after is included.

My WTD calculation currently takes the Start time, and if that is within the current week then it's included, which leaves the possibility of a driver who starts their shift Sunday night and drives through the night, not having that portion of their shift being included in the WTD figure.

My first thought would be to take this row and split into two where one row has start date/time as the original start date/time and the finish date/time is 23:59:59 Sunday, and the second row has the original finish date/time and a start date/time of 00:00:00 Monday.

Would that be the approach to take, or is there a cleaner way to do this in DAX with a measure?

My DAX WDT calc look like this:

Week to Date Minutes = 
var CurrentDate=LASTDATE('Date Table'[Date])
var DayNumberOfWeek=WEEKDAY(LASTDATE('Date Table'[Date]),3)
return
CALCULATE(
    SUM(Timesheets[totalOnDutyDriveRestDuration]),
DATESBETWEEN(
    'Date Table'[Date],
DATEADD(
    CurrentDate,
    -1*DayNumberOfWeek,
    DAY),
    CurrentDate))

[V-pazhen-msft]

@willpage 

The content is pretty long, maybe you can provide a sample pbix that I can better understand your model and show me the expected output column.

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.