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.

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

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.

  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/

14 thoughts on “Calculating Business Hours Using DAX”

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

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

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

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

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

  4. 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, BusHou