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:
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:
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