I was helping a client this past week to calculate the total business hours between a start date/time and an end date/time, taking into account the working days, public holidays and non-working weekends, etc. As is often the case, I thought it would be a great blog article that I could share with my readers. This is a pretty involved problem with lots of moving parts, so as such I have decided to record a video showing you how I solved the problem, 1 step at a time.
Edit: Note there is a better way to solve this problem. I have left this article online for the learning experience however I recommend you use the solution in my next article to solve the problem.
How Many Business Hours Has My Ticket Been Open?
I created some sample data (shown below) to explain the problem and how I solved the problem using DAX.
The sample data shows some help desk support tickets with an open date/time and an end date/time for each ticket.
Please note the following:
- The column Elapsed Hours shows the time difference between open and end times.
- It includes non-working hrs
- It includes weekends and holidays
- It is often required for the turnaround time for a support ticket needs to be calculated taking only the business hours between the start and end times ignoring non working hours, weekends and public holidays.
The Solution
This problem can be solved using DAX or using Power Query. I have chosen DAX to calculate the business hours in this case. I explain my rationale as to why in the video. As always I prefer to solve problems by breaking the problem into pieces, testing the results at every step before moving forward. I used variables in DAX in order to define each step and test it before I moved on. Using variables gives good readability and understandability of the complex DAX formulas.
Edit: Make sure you check out my follow up article here too, for a more elegant solution.
The DAX Formula
Here is the final formula, but the formula itself doesn’t really matter – you won’t learn DAX by reading this formula. You WILL learn DAX by watching my video of how I wrote this formula.
Note, I have added this as an image so it is hard to copy :-). Please use the technique here if you want to do this yourself.
Here is The Video
Here are the Sample File and Power BI Workbook
Business Hours Sample Data
Calculate business hours PBIX file
Be sure to read my follow up article here https://exceleratorbi.com.au/calculating-elapses-business-hours-using-dax-part-2/
Want to Learn DAX from Me?
If you like this video and my teaching style, why not consider enrolling in one of my formal training courses where I teach you how to be great at DAX. I have the following options.
- Live instructor led introduction training courses (designed by me and delivered by Jason).
- A live instructor led intermediate/advanced DAX course designed and delivered by me
- Video on demand versions of the above live courses
- A semester based DAX course where you learn from my book and online video content, and then join a weekly live Q&A session with me to help you learn.
Hi Matt,
I have a question : How can I do if I want to consider the beak time in business hours ? Like 9am to 12pm and 1pm to 5 pm. I don’t want to consider the lunch time between 12pm and 1pm.
Also do you have an idea about how to take into account the variable holidays like Good Friday or Easter Monday ?
My business hours are from 5 pm to 5 am. It is calculating on the same day and thus giving me negative values. Can you please help me out in this scenario
Hi Matt!
Could you please help what should be updated in DAX to add Calculation of Business Hours for different countries with different Public Holidays, so for tickets issued in Spain for example were excluded spanish Public holidays, same for Portugal etc:
Business Hours Time
Start 09:00
End 18:00
Holiday 2021 Country
New Year’s Day Friday, 1 January 2021 Spain
Good Friday Friday, 2 April 2021 Spain
Labor Day Saturday, 1 May 2021 Spain
Freedom Day Sunday, 25 April 2021 Portugal
Republic Day Tuesday, 5 October 2021 Portugal
Thanks
Hello
When i try the switch(true(), this formula stops working. I think it had to do with the related function. I changed that formula. i don’y have any tickets that are printed during non business hours.
Thanks for taking the time to share
Hello Matt,
Real great work!
fyi, The ticket 156 is calculating the wrong time.
It is on the same day, starting at 6:28 and closing at 12:14 but the table is giving 9 working hours.
Do you have a solution for that?
In the meantime, i will still continue to try to solve this.
Cheers. G.
Hi!
I think i solved those cases.
I inserted in this var two lines and adapted the sort order.
The cases are, for the same day (the comments are starting with “added”):
– both start and end are before the bus start => 0
– start before bus start, end during normal time
I also change the following line: var ThisStartTime = if(‘ARC Items Completed'[MDTeam Start Time] < BusHoursStart, BusHoursStart, 'ARC Items Completed'[MDTeam Start Time])
var FirstDayElapsedTime =
SWITCH(TRUE(),
RELATED(CalendarAutoPQ[flg Working Day])=0,0, // if the start date is a non working day, then set the time on the first day to be 0
ThisStartTime <= BusHoursStart && StartDate = EndDate && ThisEndTime <= BusHoursStart, 0 , // added case of same day ticket and end before bus start time
ThisStartTime <= BusHoursStart && StartDate = EndDate, (ThisEndTime – ThisStartTime) , // addeed case of same day ticket and start before bus time
ThisStartTime = BusHoursEnd, 0, // if the start time in on or after the end of the day, then there is no time on the first day
StartDate = EndDate && ThisEndTime < BusHoursEnd, (ThisEndTime – ThisStartTime) , // this is for when the ticket is opened and closed the same day
(BusHoursEnd – ThisStartTime) // otherwise just work out the hours
)
It is not perfect but it seems to work.
Cheers. G.
Hi Matt,
How can I make it so that if the task is not yet completed, it returns no value? Currently if there is not information in the End Time and Date, the function is still calculating through the first business day as the end value. Thank you so much for this solutions.
Thank you
Hi Matt,
First of all, that you so much for this video 🙂
Would you mind to tell me if I’m thinking correctly? 😀
Imagine the case when you have a ticket with the following dates:
StartDate = 1Dez 7:50 AM
EndDate = 1Dez 7:55 AM
The program will return 9 hours when it should return 0 (zero) hours, because not even 1 minute where spent inside the business working hour interval (8:00-17:00). For this special case I added a new line (My Code) because we have to subtract “BusHoursPerDay”.
VAR LastDayElapsedTime = SWITCH(TRUE(),
LOOKUPVALUE(‘Calendar'[Working Day],’Calendar'[Date],EndDate)=0,0,
ThisEndTime >= BusHoursEnd,BusHoursPerDay, //edited sequence to fix logic issue
StartDate = EndDate && ThisEndTime <= BusHoursStart, -1*BusHoursPerDay, // My Code
ThisEndTime <= BusHoursStart,0,
StartDate = EndDate,0,
round((ThisEndTime – BusHoursStart)*24,3)
)
Am I seeing it correctly?
Thanks!
Hi Rodrigo
It’s been a while since I looked at this and it is sufficiently complex that it is not something I can look at for 2 mins and give you a clear answer. I suggest you set up some test data and work through the variable parts, one at a time to make sure they work. This is a general principle that works for everything, even when there are mistakes to be corrected.
Also take a look at the follow up article here https://exceleratorbi.com.au/calculating-elapses-business-hours-using-dax-part-2/
If I was starting again, I would use the second approach.
Very nice video – have watched it twice now. Thanks for sharing your workflow and leaving in how you handled resolving the small issues.
I did wonder if there might be an issue with FullWorkDays where tickets were opened & closed on the same day: would the +1 & -1 in the DATESBETWEEN give a negative number? But I tried it and saw that DATESBETWEEN doesn’t return anything when this is the case. Over on docs.microsoft, this isn’t mentioned but dax.guide states that an empty table is returned when the StartDate is greater than the EndDate.
Thanks!
I’m glad it was useful. I’m hoping to have a useful follow up article this week – let’s see how busy I get 🙂
I enjoyed seeing how you deal with such a task.
ThX
Hi Matt,
Excellent presentation, thanks for putting it together.
As I watched it last night, I thought I spotted a bug in the logic. I only have the on-prem RS version of Power BI available to me at the moment, and it won’t open your example (enhanced metadata not supported or something).
I don’t have time to rebuild the thing, but staring a hole through the final resulting DAX above, I don’t think it’s correctly allowing for the situation where a ticket is opened before business hours and closed on the same day. The order of statements in the first SWITCH() means a ticket opened before business hours defaults to 9 hours for that day, and the same-day-check is never performed.
I think the 3rd line of the SWITCH() statement should come after the 4th/5th.
cheers
RET
Now I re-read Frank Tonsen’s comment, I think he was driving at the same thing.
I agree with Frank and Richard here. It seems that the order of the SWITCH statements is mixed up. I have moved the 3rd line in both SWITCH statements below the 4th line, and everything seems to be handled correctly now.
Thanks for the great work!
Thanks, and sorry for the slip. I have corrected it
If I work from 7:00 til 23:00 on a working day, your measure will return 16 business hours!
I would change 3 lines:
FirstDayElapsedTime:
StartDate EndDate && ThisStartTime <= BusHoursStart, BusHoursPerDay, //if the start time is on or before the start of day, then there are 9 hours on the first day
StartDate <> EndDate, ROUND((MIN(ThisEndTime,BusHoursEnd)-MAX(ThisStartTime,BusHoursStart)) * 24, 3), // this is for when the ticket is opened and closed on the same day
LastDayElapsedTime:
StartDate EndDate && ThisEndTime >= BusHoursEnd, BusHoursPerDay, //if the end time is on or after the end of day, then there are 9 hours on the last day
This seems to correct the issue mentioned. Hopefully, there are no other issues.
Yes, my column will return 16 business hours – that is what it is designed to do. I have tired to demonstrate “How” to solve such a problem. Different companies/people may have different requirements; I show you how to do it, then you can solve your own problem yourself. If everyone that exactly the same requirements, then there would just be a standard solution that worked for everyone.
There are no issues in my solution as you suggest, and hence there is nothing that needs correction – you just have a different requirement.
These kind of “SWITCH(TRUE, …” formulas are tricky.
If I work from 7:00 til 10:00 on a working day, your measure seems to return 9 business hours!
Yes, my example here is designed based on standard business hours – it is not designed to take into account different working hours for individual employees. If that is what you need, then you will need to build a solution that supports that.
Again an excellent tutorial/explanation!
It was very interesting to see how you populated the tables with the necessary data, making the logical process of working through the daunting challenge simple to follow!
Thank you!!