Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 @ 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?
@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?
How do you plan to handle unclean data? Like, a request raised on a sunday, or completed on a holiday ?
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)
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...
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
46 | |
28 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
52 | |
46 | |
15 | |
12 |