Calculating Business Days and Hours Between Two Dates (DAX)
Updated: Oct 20, 2022
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:
A date table including a column for [isWeekend] = WEEKDAY([Date],2)>=6
A date field for start date
A time field for the start time
A date field for the end date
A time field for the end time
Here is what the model looks like: