Antares Studio Games Icon_Small.png

The Antares Alien

Power BI   |  Game Development

  • Brent Jones

Calculating Business Days and Hours Between Two Dates (DAX)

Updated: Oct 20



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 = [Created Date]
var handledDate = [Handled Date]
var _createdTime = [Created Time]
var _handledTime = [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:


Cheers!

Support my Indie game!
Steam

Itch.Io

Cover Art - Main_Medium_With_Title_Cropp
  • Facebook
  • Twitter
  • Instagram

Support my Indie game!
Steam

Itch.Io

Cover Art - Main_Large_With_Title.png