top of page
Antares Studio Games Icon_Small.png

The Antares Alien

Power BI   |  DAX

Writer's pictureBrent Jones

Calculating Business Days and Hours Between Two Dates (DAX)

Updated: Jul 26



Power BI DAX has a function called "NETWORKDAYS()". However, it does not calculate hours. If you need to calculate hours, you can use the following DAX code.


 

NOTE: You can also do this in Power Query which I describe in this article: https://www.antaresanalytics.net/post/calculating-business-days-and-hours-between-two-dates


 

The following DAX code is for created a calculated column (not a measure!) Why? Well, because the code is somewhat complex, I figured it would be easier on the data model (Vertipaq engine) to do the calculation once at refresh time, rather than each time a visual is refreshed/changed.

Networking Hours = 

// Enter your business's information
var workingHoursInDay = 8
var endTime = TIME(17,0,0)
var startTime = TIME(8,0,0)
// ************************* //

// Replace your date columns here
var createdDate = 'DateTable'[Created Date]
var handledDate = 'DateTable'[Handled Date]
var _createdTime = 'DateTable'[Created Time]
var _handledTime = 'DateTable'[Handled Time]
var _handledTimeMod = if (WEEKDAY(handledDate,2) > 5, endTime, _handledTime)

// Clamp the created and handled times to workday hours
var createdTime = MAX(MIN(_createdTime , endTime),startTime)
var handledTime = MAX(MIN(_handledTimeMod , endTime), startTime)

// Calculate the hourly time difference
var timeDiff = 
    IF ( createdDate = handledDate,
        (handledTime - createdTime) * 24,
        (handledTime - startTime) * 24
    )

// Calculate any weekend hours (you'll need to replace your date table with this one)
var weekendHours = 
    CALCULATE(
        COUNTROWS('DateTable'),
        DATESBETWEEN('DateTable'[Date], createdDate, handledDate),
        'DateTable'[isWeekend]
    ) * workingHoursInDay

return 
    IF (
        // Return blank if no handled date exists
        ISBLANK(handledDate), BLANK(),

        // Otherwise, calculate networking hours
        INT(handledDate - createdDate) * workingHoursInDay + timeDiff - weekendHours
    )

In order to use this code, you'll need to have the following:

  1. A date table including a column for [isWeekend] = WEEKDAY([Date],2)>=6

  2. A date field for start date

  3. A time field for the start time

  4. A date field for the end date

  5. A time field for the end time


Here is what the model looks like:



 






Does DAX confuse you? It certainly did for me, until I read Supercharge for Power BI. Read about it my other article here!

 


Cheers!

Comments


  • Facebook
  • Twitter
  • Instagram
bottom of page