topic Re: Net Work Duration (Working Hours) in Quick Measures Gallery

Net Work Duration (Working Hours)

Greg_Deckler — Thu, 09 Apr 2020 16:17:25 GMT

Similar to Net Work Days, Net Work Duration calculates the total duration between two date/time columns taking into account non-working days (weekends) as well as the start and end times for a work day. This version calculates duration in minutes, although this can be easily changed.

mNetWorkDuration = 
// Get the start and end dates
VAR __dateStart = MAX([Date_Start])
VAR __dateEnd = MAX([Date_End])
// Calculate the Net Work Days between the start and end dates
VAR __NetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR(__dateStart,__dateEnd),"WeekDay",WEEKDAY([Date],2)),[WeekDay]<6),[Date])
// Set this to the start of the work day (7:30 AM)
VAR __startTime = TIME(7,30,0)
// Set this variable to the end of the work day (6:00 PM)
VAR __endTime = TIME(18,0,0)
// Calculate the duration of a full day, in this case in minutes
VAR __fullDayMinutes = DATEDIFF(__startTime,__endTime,MINUTE)
// Calculate teh number of full days, this accounts for the possibility that tickets start and end on the same day
VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
// Calculate the total duration of all full days.
VAR __fullDaysDuration = __fullDays * __fullDayMinutes 
// Calculate the start time of the current record
VAR __startDayTime = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
// Caclulate the duration of time for the first day
VAR __startDayDuration = DATEDIFF(__startDayTime,__endTime,MINUTE)
// Calculate the end time of the current record
VAR __endDayTime = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
// Calculate the duration of time for the last day
VAR __endDayDuration = DATEDIFF(__startTime,__endDayTime,MINUTE)
// The total duration is the duration of all full days plus the durations of time for the first and last days
RETURN  
IF(__NetWorkDays=1,DATEDIFF(__dateStart,__dateEnd,MINUTE),__fullDaysDuration + __startDayDuration + __endDayDuration)

eyJrIjoiYmNhYzJjY2YtZTBiMC00MDc4LThjNjAtM2YzOGE2M2JkMTdiIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9

Re: Net Work Duration (Working Hours)

NVieira — Fri, 14 Sep 2018 16:13:57 GMT

Hi,

I was trying to use this measure, but if we have dates that are outside working hours, the values are not correct ( see attachment).

Re: Net Work Duration (Working Hours)

Greg_Deckler — Fri, 14 Sep 2018 17:28:32 GMT

So, if the time is outside of the work hours, how would you want the work hours to be calculated, do you want it to ignore any non work hour time?

Re: Net Work Duration (Working Hours)

Greg_Deckler — Fri, 14 Sep 2018 17:45:52 GMT

If this works for you, I will update the Quick Measure:

mNetWorkDuration = 
// Get the start and end dates
VAR __dateStart = MAX([Date_Start])
VAR __dateEnd = MAX([Date_End])
// Calculate the Net Work Days between the start and end dates
VAR __NetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR(__dateStart,__dateEnd),"WeekDay",WEEKDAY([Date],2)),[WeekDay]<6),[Date])
// Set this to the start of the work day (7:30 AM)
VAR __startTime = TIME(7,30,0)
// Set this variable to the end of the work day (6:00 PM)
VAR __endTime = TIME(18,0,0)
// Calculate the duration of a full day, in this case in minutes
VAR __fullDayMinutes = DATEDIFF(__startTime,__endTime,MINUTE)
// Calculate teh number of full days, this accounts for the possibility that tickets start and end on the same day
VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
// Calculate the total duration of all full days.
VAR __fullDaysDuration = __fullDays * __fullDayMinutes 
// Calculate the start time of the current record
VAR __startDayTime = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
VAR __startDayTime1 = SWITCH(
                            TRUE(),
                            __startDayTime>__endTime,__endTime,
                            __startDayTime<__startTime && __startDayTime>TIME(0,0,0),__startTime,
                            __startDayTime
                        )
// Caclulate the duration of time for the first day
VAR __startDayDuration = DATEDIFF(__startDayTime1,__endTime,MINUTE)
// Calculate the end time of the current record
VAR __endDayTime = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
VAR __endDayTime1 = SWITCH(
                            TRUE(),
                            __endDayTime>__endTime,__endTime,
                            __startDayTime<__startTime && __startDayTime>TIME(0,0,0),__startTime,
                            __endDayTime
                        )
// Calculate the duration of time for the last day
VAR __endDayDuration = DATEDIFF(__startTime,__endDayTime1,MINUTE)
// The total duration is the duration of all full days plus the durations of time for the first and last days
RETURN  
IF(__NetWorkDays=1,DATEDIFF(__dateStart,__dateEnd,MINUTE),__fullDaysDuration + __startDayDuration + __endDayDuration)

Re: Net Work Duration (Working Hours)

NVieira — Mon, 01 Oct 2018 10:52:36 GMT

Hi Greg,

Thank you very much for your help.
I still get negative values when both start/end time are outside working hours.

Date Start - 07-07-2018 21:27:00

Date End - 08-08-2018 00:05:00

I get -445

In this cases i was expecting to have 0 instead of negative values.

Beside that, how can i get an average of the mNetWorkDuration?

Re: Net Work Duration (Working Hours)

eduardo_alda — Mon, 19 Nov 2018 20:49:23 GMT

Hey Greg many thanks for your inputs, could you also consider holidays , based on another "holidays" table?

Thank you so much!

Re: Net Work Duration (Working Hours)

Diego09 — Sat, 15 Jan 2022 19:19:41 GMT

do you want it to ignore any non work hour time?

YES

how do you average mNetWorkDuration?

Re: Net Work Duration (Working Hours)

Anonymous — Mon, 18 Jul 2022 20:05:22 GMT

Hi Greg,

This is just what I need, to ignore any non work hour time, and if possible calculate Saturday halftime as working day.

Re: Net Work Duration (Working Hours)

Danilo_Castillo — Mon, 21 Nov 2022 17:22:03 GMT

Greg_Deckler Hi dear friend.

I was reading the whole thread and found it very interesting. I'm just trying to do the calculation.

It would be very useful if the time is outside working hours, the duration time is taken from the next working day starting on working hours (excluding holidays, Saturday noon and Sundays).

example:

Working days: monday to friday working hours 8 am - 5 pm , saturday from 8 am - 1 pm

exclude holidays and sundays

start date: friday 11 pm
end date: monday 9 am
duration: 1 hour

I really appreciate your help

Re: Net Work Duration (Working Hours)

Greg_Deckler — Mon, 21 Nov 2022 19:44:20 GMT

Danilo_Castillo Hey Danilo!! You are in luck my friend, I wrote a Working Hours version of this! Net Work Duration (Working Hours) - Microsoft Power BI Community

Might not be exactly what you need but probably between the two we can get you there! Let me know!

Re: Net Work Duration (Working Hours)

spriyanshu — Thu, 18 May 2023 10:55:14 GMT

Hi Greg_Deckler
I am trying to use your code for same kind of calculation but no need to account for holidays. Will bottom modification willl work.
My Use case: i just want to calculate time (in minutues) used in maintenance window between 1 am to 6 am.

measure =

VAR __dateStart = MAX([INCIDENT STARTED])

VAR __dateEnd = MAX([SERVICE_RESTORED])

VAR __startTime = TIME(1,0,0)

VAR __endTime = TIME(6,0,0)

VAR __fullDayMinutes = DATEDIFF(__startTime,__endTime,MINUTE)

VAR __startDayTime = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))

VAR __startDayDuration = DATEDIFF(__startDayTime,__endTime,MINUTE)

VAR __endDayTime = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))

VAR __endDayDuration = DATEDIFF(__startTime,__endDayTime,MINUTE)

RETURN

IF(DATEDIFF(__dateStart,__dateEnd,MINUTE), __startDayDuration + __endDayDuration)