Datediff taking into account our service window

[broeren79]

Hi guys, i have a question about calculating the time between 2 date/time values.

The complexity in my case is that our team has a service window between 8:00 and 17:00 on working days. Now i need to calculate the duration between 2 date/time values in seconds or minutes taking into account the service window.

For example, if a request starts on Monday 16:59 and is resolved on Tuesday 08:59, the duration was 60 minutes.

If the request starts on Friday 16:59 and is resolved on Tuesday 08:59 and Monday was a national holiday, the duration was 60 minutes.

How do i approach that?

[Anonymous]

@broeren79 Did this solve your issue? 

[broeren79]

@Anonymous @ solution works, for most cases, but there is indeed an issue in case a datestamp falls outside of the servicewindow. This does happen in cases when an employee decides to start early, or when a ticket is handled outside the service hours.

I think it makes sense that, in case a ticket is reported outside the servicewindow, time should start counting as per the start of the next day. e.g. ticket starts 02-08-2020 23:38:10, it should start counting at 03-08-2020 08:30.
In case a ticket is closed outside of the service window, it should be treated similarly, e.g: 03-08-2020 07:50:21 should be counted as 03-08-2020 08:30.

How do i change the statement so this is incorporated?

[broeren79]

@Greg_Decklercan you maybe shine light on how i would solve the issue above, concerning de case where due to exceptions time stamps are measured outside working days?

[lbendlin]

How do you plan to handle unclean data? Like, a request raised on a sunday, or completed on a holiday ?

[Anonymous]

To work out the minute's difference between working hours Monday to Friday (so minus Weekends) Create a calculated column and paste the following (adjusting the highlighted text below to reference your columns)

VAR __dateStart = 'Table (2)'[Start time]

VAR __dateEnd = 'Table (2)'[End time]

Credit to @Greg_Deckler for creating the original 
https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration-Working-Hours/m-p/481543#M... 

@broeren79

Minutes diffrence = 
VAR __dateStart = 'Table (2)'[Start time]
VAR __dateEnd = 'Table (2)'[End time]
VAR __NetWorkDays = COUNTX(FILTER(ADDCOLUMNS(CALENDAR(__dateStart,__dateEnd),"WeekDay",WEEKDAY([Date],2)),[WeekDay]<6),[Date])
VAR __startTime = TIME(8,00,0)
VAR __endTime = TIME(17,0,0)
VAR __fullDayMinutes = DATEDIFF(__startTime,__endTime,MINUTE)
VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
VAR __fullDaysDuration = __fullDays * __fullDayMinutes 
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(__NetWorkDays=1,DATEDIFF(__dateStart,__dateEnd,MINUTE),__fullDaysDuration + __startDayDuration + __endDayDuration)