How to Calculate hours difference between two dates/times taking into consideration working hours

[Anonymous]

How can i calculate the difference in hours (Minutes as well if possible) taking into consideration that working hour is:

From 9:00:00

To 17:00:00

I have searched online but these topics do not answer my questions directly.

I have tried to use DATEDIFF Function which works as measure but I do not know how to include only working hours in this case.

DATEDIFF( MIN(Incident.dataaanmk); MIN(Datwijzig); HOUR)

I am using MIN because an incident may have:

- Only 1 Dataaanmk

- But many Datwijzig (We only take the first one).

Thank you.

[Anonymous]

@Anonymous 

I am not sure about your expected result. If you want to calculate the difference between two time in HH:MM:SS. You could just create a column: column =  [Time column1]-[Time column2], then change the new new column into time type.

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Greg_Deckler]

Try something along the lines of:

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 __startHour = TIME(7,30,0)
// Set this variable to the end of the work day (6:00 PM)
VAR __endHour = TIME(18,0,0)
// Calculate the duration of a full day, in this case in minutes
VAR __fullDayMinutes = DATEDIFF(__startHour,__endHour,MINUTE)
VAR __fullDays = IF(__NetWorkDays < 2,0,__NetWorkDays-2)
VAR __fullDaysDuration = __fullDays * __fullDayMinutes 
VAR __startDayHour = TIME(HOUR(__dateStart),MINUTE(__dateStart),SECOND(__dateStart))
VAR __startDayDuration = DATEDIFF(__startDayHour,__endHour,MINUTE)
VAR __endDayHour = TIME(HOUR(__dateEnd),MINUTE(__dateEnd),SECOND(__dateEnd))
VAR __endDayDuration = DATEDIFF(__startHour,__endDayHour,MINUTE)
RETURN  __fullDaysDuration + __startDayDuration + __endDayDuration

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Duration/m-p/481543#M182

[Anonymous]

@Greg_Deckler 

Thank you for your answer.

I have applied the measure but it takes too long to to apply when using it in a table.

[Greg_Deckler]

So does it work? How long is too long? Potentially could be optimized somehow.