Sample Solution: DAX for Business Hour Calc

In this example, the employer needed their dashboard in Power BI to exclude non-business hours (BH) when running the “Time to Close” (TTC) calculation, which is the time the ticket is opened ’til when it is closed. Here is the solution we created, based on existing code examples, the client’s needs, and our own ingenuity. It is written in DAX, which allowed the dashboard to update dynamically as the user clicked between different visualizations.

TTC BH = 

    VAR BusHoursStart = CALCULATE(SELECTEDVALUE(BusinessHours[Time EST]), BusinessHours[Business Hours]="Start")
    VAR BusHoursEnd = CALCULATE(SELECTEDVALUE(BusinessHours[Time EST]), BusinessHours[Business Hours]="End")
    VAR BusMinutesPerDay = VALUE(BusHoursEnd - BusHoursStart) * 24 * 60  // Calculate minutes instead of hours

    VAR ThisStartTime = 'external-support-tickets'[Created Time HH:MM]
    VAR ThisEndTime = 'external-support-tickets'[Completed Time HH:MM]
    VAR StartDate = 'external-support-tickets'[Created Date]
    VAR EndDate = IF(
        ISBLANK('external-support-tickets'[Completed Date]), 
        TODAY(), 
        'external-support-tickets'[Completed Date]
         )

    //Calculate First Day's Elapsed Minutes
    VAR FirstDayElapsedTime = SWITCH(TRUE(),
    //Check if ticket is unresolved.  While this technically doesn't account for non-working days, we shouldn't be running this on nonworking days, so to keep the calc faster, leaving out that logic should be fine.
    ISBLANK(ThisEndTime) && StartDate = TODAY(), (MOD(NOW(), 1) - BusHoursStart) * 24 * 60,
    ISBLANK(ThisEndTime) && StartDate <> TODAY(),BusMinutesPerDay,
    //Check for working days
    RELATED('Date'[Working Day])=0,0, //This condition checks if the start date of the ticket (StartDate) falls on a non-working day (e.g., weekends or holidays) based on your Date table's [Working Day] field.  That makes the time 0 for that day.
    ThisStartTime>=BusHoursEnd,0, //If the start time is after business hours end, no minutes are counted for the first day.
    // Tickets start before business hours (ADDED: and don't end on the same business day), count the full day.
    ThisStartTime < BusHoursStart && EndDate > StartDate, BusMinutesPerDay, 
    //Tickets start before business hours but end during business hours (ADDED, REORDERED).
    ThisStartTime < BusHoursStart && ThisEndTime >= BusHoursStart && ThisEndTime <= BusHoursEnd, (ThisEndTime - BusHoursStart) * 24 * 60,
    // Tickets opened and closed on the same day (during business hours), calculate exact minutes.
    StartDate = EndDate && ThisEndTime <= BusHoursEnd, (ThisEndTime - ThisStartTime) * 24 * 60,
    // Calculate minutes from start time to business end if the ticket does not fit previous conditions. This now just handles the case where the ticket is resolved after business hours but still on the same day as the start date.  Only business hours should be counted.
    (BusHoursEnd - ThisStartTime) * 24 * 60
    )

    //Calculate Last Day's Elapsed Minutes
    VAR LastDayElapsedTime = SWITCH(TRUE(), //currently including nulls for resolved date (ticket still open).  Is this a problem?  How to address?
    //Check if ticket is still opened
    ISBLANK(ThisEndTime) && StartDate <> TODAY(), (MOD(NOW(), 1) - BusHoursStart) * 24 * 60, // Calculate elapsed business minutes for today for tickets that are not yet resolved.  While this would technically show on non-working days, we shouldn't be caring about running the dashboards on non-running days, so for the sake of keeping the formula as clean as possible, this should be fine.
    RELATED('Date'[Working Day])=0,0, //This condition checks if the start date of the ticket (StartDate) falls on a non-working day (e.g., weekends or holidays) based on your Date table's [Working Day] field.  That makes the time 0 for that day.
    StartDate = EndDate,0, // this is for when the ticket is opened and closed on the same day.  Don't want to duplicate the hours from the first day calc.  So from here out, other scenarios only consider tickets opened and closed on different days.
    ThisEndTime < BusHoursStart,0,  // if the end time is on or before the start of day (and ticket was opened and closed on different days), then there is no time on the last day
    ThisEndTime >= BusHoursEnd,BusMinutesPerDay,  //if the end time is on or after the start of day (and ticket was opened and closed on different days), then there are X hours on the last day (where X is the number of business hours per day)
    (ThisEndTime - BusHoursStart) * 24 * 60  // Calculate minutes from business start to ticket end time for any other case
    )

    //Calculate dates between start and end date for final calc
    VAR FullWorkDays = CALCULATE(sum('Date'[Working Day]),DATESBETWEEN('Date'[Date], StartDate+1,EndDate-1)) //unresolved showing as null

    VAR TotalMinutes = FirstDayElapsedTime + FullWorkDays*BusMinutesPerDay + LastDayElapsedTime 
    
    RETURN TotalMinutes/60

Step 1: Define Business Hours Variables

1. BusHoursStart – Retrieves the starting time of business hours from the BusinessHours table.
2. BusHoursEnd – Retrieves the ending time of business hours from the BusinessHours table.
3. BusMinutesPerDay – Calculates the total number of business minutes in a day by taking the difference between BusHoursEnd and BusHoursStart and converting it into minutes.

Step 2: Retrieve Ticket Start and End Times

4. ThisStartTime – The specific start time of the ticket from the Created Time HH:MM column.
5. ThisEndTime – The specific end time of the ticket from the Completed Time HH:MM column.
6. StartDate – The start date of the ticket.
7. EndDate – The end date of the ticket, which defaults to TODAY() if the Completed Date is blank (indicating the ticket is still open).

Step 3: Calculate Elapsed Minutes for the First Day of the Ticket

8. FirstDayElapsedTime – Calculates the business minutes for the first day using a series of conditions:
   • If the ticket is unresolved and was created today, calculate time from BusHoursStart to the current time.
   • If unresolved but created on a prior date, count the full business day.
   • If the start date is a non-working day, set the elapsed time to 0.
   • If the ticket start time is after business hours end, set the elapsed time to 0.
   • If it starts before business hours and spans multiple days, count the full day’s minutes.
   • If it starts before business hours and ends within business hours, calculate the exact elapsed minutes.
   • If it starts and ends on the same day during business hours, calculate the exact elapsed minutes.
   • For other cases, count from the ticket’s start time to business end.

Step 4: Calculate Elapsed Minutes for the Last Day of the Ticket

9. LastDayElapsedTime – Calculates the business minutes for the last day using a series of conditions:
   • If unresolved but created on a prior date, calculate time from BusHoursStart to the current time.
   • If the last day is a non-working day, set the elapsed time to 0.
   • If opened and closed on the same day, set the elapsed time to 0 (to avoid double-counting).
   • If the end time is before business hours start, set the elapsed time to 0.
   • If it ends after business hours, count the full day.
   • For other cases, count from the business start to the ticket’s end time.

Step 5: Calculate Full Working Days

10. FullWorkDays – Counts the number of full working days between the start and end dates, excluding the first and last days, using the Working Day field from the Date table.

Step 6: Calculate Total Minutes

11. TotalMinutes – Adds together:
    • FirstDayElapsedTime (elapsed minutes for the first day),
    • FullWorkDays (multiplied by daily business minutes for full days), and
    • LastDayElapsedTime (elapsed minutes for the last day).

Step 7: Convert to Hours and Return

12. The formula returns TotalMinutes divided by 60, converting the result from minutes to hours.