Calculating Business Hours Using DAX - Excelerator BI

Calculating Business Hours Using DAX

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.

Calc Bus Hrs 1

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.

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>=BusHoursEnd,0,
                                ThisStartTime <= BusHoursStart,BusHoursPerDay,
                                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<=BusHoursStart,0,
                                 ThisEndTime >= BusHoursEnd,BusHoursPerDay,
                                 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

Business Hours Sample Data
Calculate business hours PBIX file

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.

  1. Live instructor led introduction training courses (designed by me and delivered 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 delivered by me
    1. https://exceleratorbi.com.au/product-category/virtual-instructor-led-online-training/module-3/
  3. Video on demand versions of the above 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 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.
    1. https://www.skillwave.training/courses/supercharge-power-bi-online/

10 thoughts on “Calculating Business Hours Using DAX”

  1. 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!

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

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

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

  4. 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!

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

  5. 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!!

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top