*UPDATE: 8/4/2022
Power BI DAX now has a function called "NETWORKDAYS()". However, it does not calculate hours. If you want to get networking hours using DAX, view my article here: https://www.antaresanalytics.net/post/calculating-business-days-and-hours-between-two-dates-dax
Here is a Power Query custom function to calculate the number of business (networking) hours between two dates. All you need to do is just copy and paste this code into a blank query! You shouldn't need to change anything. Very easy!
This excludes weekends and non-business hours using a Power Query custom function. This can then be loaded into the Power BI data model.
// Pass in the datetime columns for first and second date
// Enter a number for the start of business hour. i.e. 9 for 9:00 AM
// Enter a number for the close of business hour. i.e. 17 for 5:00 PM
(
firstdate as datetime,
seconddate as datetime,
startofbusiness as number,
closeofbusiness as number
) as number =>
let
// Use a Looping function to Generate hourly intervals
y = List.Generate(() =>
firstdate,
each _ <= seconddate,
each _ + #duration(0,0,30,0)),
// Convert this list to a table
#"Converted to Table" =
Table.FromList(
y, Splitter.SplitByNothing(),
null, null, ExtraValues.Error),
#"Changed Type" =
Table.TransformColumnTypes(
#"Converted to Table",{{"Column1", type datetime}}),
#"Added Custom" =
Table.AddColumn(
#"Changed Type", "WeekDay", each
Date.DayOfWeek([Column1]), Int32.Type),
// Filter out weekend days
#"Filtered Rows" = Table.SelectRows(
#"Added Custom", each
([WeekDay] <> 0 and [WeekDay] <> 6)),
#"Added Custom2" = Table.AddColumn(
#"Filtered Rows", "Time", each
DateTime.Time([Column1])),
// Filter out non-business hours
#"Filtered Rows1" = Table.SelectRows(
#"Added Custom2", each
[Time] > #time(startofbusiness, 0, 0) and
[Time] <= #time(closeofbusiness, 0, 0)),
// Count rows (half hour intervals), divide by 2 to get hours
rowcount = Number.Round(Table.RowCount(#"Filtered Rows1")/2,1),
returncount = if rowcount<0 then 0 else rowcount
in
returncountI tried other methods online but they just didn't work. They were too complicated and involved creating separate tables. This method simply creates a custom function that you can use (and re-use) in the Power Query function, Table.AddColumn() .
How to implement:
1. Create a blank query
2. Open the advanced editor
3. Clear out the code
4. Copy and paste this code
5. Rename the query
6. Create a custom column
Keep in mind you'll need to remove or replace Error that result if you have any rows with missing values. This can be easily done using the "Replace Errors" function in Power Query.
One caveat...
One slight caveat is it calculates hours using 30 minute intervals. It's certainly possible to modify this code using single minute increments, however this runs the risk of some serious overhead. The bigger the difference between your two dates, the longer it will take to process. I.e. the current code generates 16 items for every 1 work day (8 time 30 minutes). If you were to use minutes, the code would end up generating 480 items for every 1 work day (60 * 8). This may be fine if your dates are not too far apart. However, if your dates are something like 1 year apart, you'll end up generating 124,800 items (60 minutes * 2080 hours per year). Keep in mind this is applied to each row you have in your data set!
Excluding Holidays
We can extend this custom function to also remove holidays. This solution requires one extra table for holidays. Just a simple, one column table like this (obviously you can add more rows on this table for more holidays, I just have one for simplicity):
The updated code will now look like this. Notice there is one more parameter to pass in for the holiday table. Note that the Holiday table column named MUST be named "Holiday" for this to work.
// Pass in the datetime columns for first and second date
// Enter a number for the start of business hour. i.e. 9 for 9:00 AM
// Enter a number for the close of business hour. i.e. 17 for 5:00 PM
// Pass in the holiday table
(
firstdate as datetime,
seconddate as datetime,
startofbusiness as number,
closeofbusiness as number,
holidays as table
) as number =>
let
// Use a Looping function to Generate hourly intervals
y = List.Generate(() =>
firstdate,
each _ <= seconddate,
each _ + #duration(0,0,30,0)),
// Convert this list to a table
#"Converted to Table" =
Table.FromList(
y, Splitter.SplitByNothing(),
null, null, ExtraValues.Error),
#"Changed Type" =
Table.TransformColumnTypes(
#"Converted to Table",{{"Column1", type datetime}}),
#"Added Custom" =
Table.AddColumn(
#"Changed Type", "WeekDay", each
Date.DayOfWeek([Column1]), Int32.Type),
// Filter out weekend days
#"Filtered Rows" = Table.SelectRows(
#"Added Custom", each
([WeekDay] <> 0 and [WeekDay] <> 6)),
#"Added Custom2" = Table.AddColumn(
#"Filtered Rows", "Time", each
DateTime.Time([Column1])),
// Filter out non-business hours
#"Filtered Rows1" = Table.SelectRows(
#"Added Custom2", each
[Time] > #time(startofbusiness, 0, 0) and
[Time] <= #time(closeofbusiness, 0, 0)),
// Convert DateTime column to raw date
#"Extracted Date" = Table.TransformColumns(
#"Filtered Rows1",{{"Column1", DateTime.Date, type date}}),
// Merge on holiday rows with Left-Anti join
// (effectively filtering out the holidays)
#"Merged Queries" = Table.NestedJoin(
#"Extracted Date",
{"Column1"}, holidays,
{"Holiday"}, "Holidays",
JoinKind.LeftAnti),
// Count rows (half hour intervals), divide by 2 to get hours
rowcount = Number.Round(Table.RowCount(#"Merged Queries")/2,1),
returncount = if rowcount<0 then 0 else rowcount
in
returncount
Follow the steps above to implement the code. The function will look something like this, where "Holidays" is the name of the holiday table:
= NetworkDaysNoHoliday([Created Date],[Handled Date],9,17,Holidays)Did this code help you? Please share with someone who you think would also use this!
Comentarios