Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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).

 

Capture.PNG

Thank you.

4 REPLIES 4
V-pazhen-msft
Community Support
Community Support

@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.

example time difference.JPG

 

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

@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.

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.