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.
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.
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.
Bus Hours Open = VAR BusHoursStart = CALCULATE( SELECTEDVALUE(BusinessHours[Time]), BusinessHours[Business Hours]="Start" ) VAR BusHoursEnd = CALCULATE(SELECTEDVALUE(BusinessHours[Time]),BusinessHours[Business Hours]="End") VAR BusHoursPerDay = VALUE(BusHoursEnd - BusHoursStart)*24 VAR ThisStartTime = HelpDeskTickets[Start Time] VAR ThisEndTime = HelpDeskTickets[End Time] VAR StartDate = HelpDeskTickets[Start Date] VAR EndDate = HelpDeskTickets[End Date] VAR FirstDayElapsedTime = SWITCH(TRUE(), RELATED('Calendar'[Working Day])=0,0, ThisStartTime <= BusHoursStart,BusHoursPerDay, //edited sequence to fix logic issue ThisStartTime>=BusHoursEnd,0, StartDate = EndDate && ThisEndTime < BusHoursEnd, round((ThisEndTime-ThisStartTime)*24,3), round((BusHoursEnd-ThisStartTime)*24,3) ) VAR LastDayElapsedTime = SWITCH(TRUE(), LOOKUPVALUE('Calendar'[Working Day],'Calendar'[Date],EndDate)=0,0, ThisEndTime >= BusHoursEnd,BusHoursPerDay, //edited sequence to fix logic issue ThisEndTime<=BusHoursStart,0, StartDate = EndDate,0, round((ThisEndTime - BusHoursStart)*24,3) ) VAR FullWorkDays = CALCULATE( SUM('Calendar'[Working Day]), DATESBETWEEN('Calendar'[Date], StartDate+1,EndDate-1) ) VAR TotalHours = FirstDayElapsedTime + FullWorkDays*BusHoursPerDay + LastDayElapsedTime RETURN TotalHours
Here is The Video
Here are the Sample File and Power BI Workbook
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.