Calculation of working hours with DAX

I was helping a client last week to calculate the total working hours between a start date / time and an end date / time, taking into account working days, holidays and non-working weekends, etc. As is often the case, I thought it would be a great blog post that I could share with my readers. This is quite a complicated problem with many moving parts, so I have decided to shoot a video showing 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 it using DAX.

The sample data shows some help desk support tickets with an open date / time and an end date / time for each ticket.

Consider the following:

• The Elapsed Hours column shows the time difference between the opening and ending times.
  • Includes non-working hours
  • Includes weekends and holidays
• It is often necessary for the response time of a support ticket to be calculated by taking only the business hours between the start and end times, ignoring non-business hours, weekends and holidays.

The solution

This problem can be solved with DAX or with Power Query. I have chosen DAX to calculate business hours in this case. I explain my reason for being in the video. As always, I prefer to solve problems by breaking them into pieces, testing the results at each step before moving on. I used variables in DAX to define each step and test it before proceeding. The use of variables provides good readability and understanding of complex DAX formulas.

Edit: be sure to check out my follow-up article here too, for a more elegant solution.

The DAX formula

Here's the final formula, but the formula itself doesn't really matter - you won't learn DAX by reading this formula. Your YOU WILL LEARN DAX 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 [EndDesk] VAR StartDate = HelpDeskTickets [Start Date] VAR EndDate (HelpDeskTickets [EndITlapsedTime] VAR EndDate (HelpDeskTickets [EndITlapsedTimeTime] ), 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 is the sample file and Power BI workbook

Sample business hours data
Calculate PBIX file of business hours

Be sure to read my follow-up article here https://exceleratorbi.com.au/calculating-elapses-business-hours-using-dax-part-2/

Do you 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 excellent at DAX. I have the following options.

1. Instructor-led live introductory courses (designed by me and taught by Jason).
   1. https://exceleratorbi.com.au/product-category/virtual-instructor-led-online-training/modules-1-2/
2. A live instructor-led intermediate / advanced DAX course designed and taught by me
   1. https://exceleratorbi.com.au/product-category/virtual-instructor-led-online-training/module-3/
3. Video-on-demand versions of previous live courses
   1. https://www.skillwave.training/courses/data-to-dashboard-using-power-bi/
   2. https://www.skillwave.training/courses/extracting-data-insights-with-power-bi-and-dax/
4. A semester DAX course where you learn from my book and video content online, then join a weekly live question and answer session with me to help you learn.
   1. https://www.skillwave.training/courses/supercharge-power-bi-online/