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
naomi_j
Frequent Visitor

Calculate working hours between two dates

I'm trying to calculate the number of working hours/minutes between two dates (ie. excluding non-working hours and weekends).  

 

This is the basic format - and I'm trying to find out how long it takes for an issue to get resolved, in working hours/minutes.  Can anyone help, please?

 

Issue idCreatedResolved
126540003/03/2020 13:5403/03/2020 14:21
126525603/03/2020 12:5403/03/2020 13:45
126380103/03/2020 09:0903/03/2020 09:11
126378903/03/2020 08:4203/03/2020 10:50
126232102/03/2020 17:1203/03/2020 09:18
126198502/03/2020 11:5402/03/2020 11:57
125873528/02/2020 22:2702/03/2020 16:53
125868728/02/2020 16:2603/03/2020 10:12
125863828/02/2020 14:5402/03/2020 16:00
11 REPLIES 11
Laila92
Helper V
Helper V

I found the solution in this post really clear and helpful: https://addendanalytics.com/calculate-working-hours-in-power-bi/

jolewis
Regular Visitor

Hi,

 

Looking at calculate minutes between a ticket was created and closed.

 

The measure needs to consider the following:

- Should ignore public holidays (Have a table called public holidays in which the date column has the days I have as public holidays)

- Should consider Mon to Friday

- Business Hours 7 am to 5 pm

- If completed is greater than created then should take it as 0

 

Step created at AEDT           Step Completed at AEDT

15/6/21 11:32:24 am             16/6/21 2:22:46 pm

 

Any help will be appreciated

Greg_Deckler
Super User
Super User

See if these help:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Net-Work-Days/m-p/367362#M109

 

https://community.powerbi.com/t5/Quick-Measures-Gallery/Hour-Breakdown/m-p/625085#M306


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

@Greg_Deckler - I tried your net workdays option and it worked (thankfully no negative numbers output)!  Thanks!  

 

I changed the output to hours, which works better for me - although this seems to round off rather than offering part hours.  Do you know if there's a way to have the output in part-hours (eg. 1.25 for 1hr12mins instead of 1)?

@naomi_j - Not entirely certain exactly what your code is but perhaps try increasing the number of decimals?

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

@Greg_Deckler see code below.  I tried to increase the number of decimals, but this doesn't work after the calculation is complete (.00 for all values).  
 
 
hoursNetworkDuration =
// Get the start and end dates
VAR __dateStart = Master_Appended[Created].[Date]
VAR __dateEnd = Master_Appended[ResolvedNoBlanks].[Date]
// 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 (8:00 AM)
VAR __startTime = TIME(8,0,0)
// Set this variable to the end of the work day (5:00 PM)
VAR __endTime = TIME(17,0,0)
// Calculate the duration of a full day, in this case in minutes
VAR __fullDayhours = DATEDIFF(__startTime,__endTime,HOUR)
// 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 * __fullDayhours
// 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,HOUR)
// 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,HOUR)
// 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,HOUR),__fullDaysDuration + __startDayDuration + __endDayDuration)

I think if you do something like:

IF(__NetWorkDays=1,DATEDIFF(__dateStart,__dateEnd,MINUTE) / DATEDIFF(__dateStart,__dateEnd,HOUR) / 60,__fullDaysDuration + __startDayDuration + __endDayDuration)

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

😞 still gives me whole hour output (.00).  

 

Thanks Greg - it seems the Net Work Days option should be close to what I want.  Although I will have the same issue as the other user who commented in the thread (start and finish times potentially occurring out of work hours).  Was there a solution for avoiding the negative numbers?

 

Thanks again!

@naomi_j 

 

You may calculate working hours/minutes since Mon, Feb 24, 2020.

DIFF ( <Base>, <Resolved> ) - DIFF ( <Base>, <Created> )

 

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for your response, @v-chuncz-msft.  I've not seen this option - are there any articles to explain the function?  


What would be the <Base> value here?

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.