topic Re: Working Hours Between two date/time fields in DAX Commands and Tips

Working Hours Between two date/time fields

Keston — Sun, 08 Sep 2024 17:31:15 GMT

Hello,

Seeking the assistance of the forum, I have two date/time fields in the following format and the following script to calculate the working hours between 8 - 1700 excluding weekend. The script is calculating same day correctly however, if the dates spans between multiple days the hours are incorrect as see below.

Can someone help me modify this script or provide a solution for me? I'm really struggling here to get the working hours.

Same day calculation

Mutiple days

Script

WorkingHoursDuration =
VAR StartDateTime = [date/time]
VAR EndDateTime = [date/time]

-- Define working hours
VAR StartOfWorkday = TIME(8, 0, 0)
VAR EndOfWorkday = TIME(17, 0, 0)

-- Helper function to calculate working hours in a given interval
VAR CalculateWorkingHoursInInterval =
VAR IntervalStart = MAX(DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + StartOfWorkday, StartDateTime)
VAR IntervalEnd = MIN(DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + EndOfWorkday, EndDateTime)
RETURN
IF (
IntervalEnd > IntervalStart,
DATEDIFF(IntervalStart, IntervalEnd, MINUTE) / 60.0,
0
)

-- Adjust start and end times if they fall outside working hours
VAR AdjustedStartTime =
IF (
WEEKDAY(StartDateTime, 2) > 5 || StartDateTime < DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + StartOfWorkday,
DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + StartOfWorkday,
IF (
StartDateTime > DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + EndOfWorkday,
DATE(YEAR(StartDateTime), MONTH(StartDateTime), DAY(StartDateTime)) + EndOfWorkday,
StartDateTime
)
)

VAR AdjustedEndTime =
IF (
WEEKDAY(EndDateTime, 2) > 5 || EndDateTime > DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime)) + EndOfWorkday,
DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime)) + EndOfWorkday,
IF (
EndDateTime < DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime)) + StartOfWorkday,
DATE(YEAR(EndDateTime), MONTH(EndDateTime), DAY(EndDateTime)) + StartOfWorkday,
EndDateTime
)
)

-- Calculate working hours if the interval is within the same day
VAR SameDayHours =
IF (
DATEVALUE(AdjustedStartTime) = DATEVALUE(AdjustedEndTime),

0
)

-- Calculate working hours for full working days between start and end dates
VAR FullDaysStart = DATE(YEAR(AdjustedStartTime), MONTH(AdjustedStartTime), DAY(AdjustedStartTime)) + 1
VAR FullDaysEnd = DATE(YEAR(AdjustedEndTime), MONTH(AdjustedEndTime), DAY(AdjustedEndTime)) - 1

VAR FullDaysHours =
IF (
FullDaysStart <= FullDaysEnd,
CALCULATE (
SUMX (
FILTER (
CALENDAR (FullDaysStart, FullDaysEnd),
WEEKDAY([Date], 2) <= 5
),
9
)
),
0
)

-- Calculate working hours for the start and end days
VAR StartDayHours =
IF (
DATEVALUE(AdjustedStartTime) < DATE(YEAR(AdjustedStartTime), MONTH(AdjustedStartTime), DAY(AdjustedStartTime)) + EndOfWorkday,
VAR IntervalStart = MAX(StartOfWorkday, AdjustedStartTime)
VAR IntervalEnd = DATE(YEAR(AdjustedStartTime), MONTH(AdjustedStartTime), DAY(AdjustedStartTime)) + EndOfWorkday
RETURN
CalculateWorkingHoursInInterval,
0
)

VAR EndDayHours =
IF (
DATEVALUE(AdjustedEndTime) > DATE(YEAR(AdjustedEndTime), MONTH(AdjustedEndTime), DAY(AdjustedEndTime)) + StartOfWorkday,
VAR IntervalStart = DATE(YEAR(AdjustedEndTime), MONTH(AdjustedEndTime), DAY(AdjustedEndTime)) + StartOfWorkday
VAR IntervalEnd = MIN(EndOfWorkday, AdjustedEndTime)
RETURN
CalculateWorkingHoursInInterval,
0
)

-- Total working hours
VAR TotalWorkingHours =
SameDayHours + FullDaysHours + StartDayHours + EndDayHours

RETURN
MAX(0, TotalWorkingHours)

Re: Working Hours Between two date/time fields

lbendlin — Sun, 08 Sep 2024 20:09:41 GMT

There's no need for any of this, you can use a simple INTERSECT.

BUT - only if you clearly define what "working hours" means - which days, which time zones etc. Do you have that information?

Re: Working Hours Between two date/time fields

Keston — Sun, 08 Sep 2024 20:14:11 GMT

I have the details, so working hours is 8 - 1700 GMT-4 Monday to Fridday.

Re: Working Hours Between two date/time fields

lbendlin — Sun, 08 Sep 2024 20:37:40 GMT

Good. What is your required granularity level - half hour slots ok, or does it need to go down to minute level?

Please provide a couple of sample intervals in usable format, not as a screenshot.

Re: Working Hours Between two date/time fields

Keston — Sun, 08 Sep 2024 20:40:24 GMT

Down to the minute will be ideal.

Re: Working Hours Between two date/time fields

lbendlin — Sun, 08 Sep 2024 21:59:26 GMT

This is immutable data so I created a calculated column.

Work hours = var workingdays = FILTER(CALENDAR([Created],[Assigned]),WEEKDAY([Date],2)<6) var possibleworkingminutes = SELECTCOLUMNS(GENERATE(workingdays,GENERATESERIES([Date]*1440+480,[Date]*1440+1019)),"Value",[Value]) var fullinterval = GENERATESERIES(ROUNDDOWN([Created]*1440,0),ROUNDDOWN([Assigned]*1440,0)-1) var actualworkingminutes = INTERSECT(possibleworkingminutes,fullinterval) RETURN ROUNDDOWN(countrows(actualworkingminutes)/60,0) & Format(countrows(actualworkingminutes)/1440,":nn")

Re: Working Hours Between two date/time fields

Keston — Sun, 08 Sep 2024 23:30:29 GMT

Thank you for this but for some strange reason I'm receiving the folowing error.

The start date in Calendar function can not be later than the end date.

Re: Working Hours Between two date/time fields

lbendlin — Sun, 08 Sep 2024 23:40:10 GMT

You and I have different date formats. You use dd/mm/yyyy and I use mm/dd/yyyy. Neither are good formats. Best to use ISO-8601 to avoid ambiguity.

Re: Working Hours Between two date/time fields

Keston — Tue, 10 Sep 2024 17:13:59 GMT

Hey lbendlin

I'm still receiving this error: The start date in Calendar function can not be later than the end date. Is it possible to update the script to probably exclude these instances?

Thanks.

Re: Working Hours Between two date/time fields

lbendlin — Tue, 10 Sep 2024 17:20:35 GMT

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Re: Working Hours Between two date/time fields

Keston — Tue, 24 Sep 2024 23:43:58 GMT

Hi lbendlin,

I found an issue with my data, it appears that the assigned time field may be blank at times. I also simulated with the Work hours.pbix. How can we take this into consideration?

Thank you for your support.

Re: Working Hours Between two date/time fields

lbendlin — Tue, 24 Sep 2024 23:51:33 GMT

You can choose to ignore these rows, or replace the blanks with an appropriate date. Not sure if "Today" is appropriate for this?

Re: Working Hours Between two date/time fields

Keston — Fri, 27 Sep 2024 01:20:41 GMT

Hey lbendlin,

So I added Now() to the blank field and this worked well.

However, now I observed the root of the issue, I have a few instances were the created date is later than the assigned date, I'm not sure why this would have occured.

But, do you have any recommendations to the script to exlucde these instances?

Re: Working Hours Between two date/time fields

lbendlin — Fri, 27 Sep 2024 10:36:34 GMT

You could use ABS() to always get a positive duration.

Re: Working Hours Between two date/time fields

Keston — Fri, 27 Sep 2024 14:58:18 GMT

Ok noted, just to be clear, I should replace CALENDAR() with ABS()?

Re: Working Hours Between two date/time fields

lbendlin — Fri, 27 Sep 2024 15:04:15 GMT

var workingdays = FILTER(CALENDAR(MIN([Created],[Assigned]),MAX([Created],[Assigned])),WEEKDAY([Date],2)<6)