There have been a lot of questions on the forum lately about the best way to calculate number of workdays not including holidays between two dates. Most of the discussion has focused on DAX-based solutions. This is totally doable via DAX, but as you’ve seen from the recent threads not particularly simple. Well, a much easier solution came out recently in the BIccountant blog:
This article provides the Power Query code (attached below for convenience) for a custom function that calculates net working days between two dates and allows you to reference a holiday table to remove those dates as well from the calculation.
You can actually add this function as a column in Sam’s date table, by selecting Add Column and then Invoke Custom Function:
Before doing this however, I recommend first creating your holiday table using the web connector
To produce a table like this:
where you can then reference the Holiday Date column as a parameter in the NETWORKDAYS custom function.
Once you have this all set up, all you need to do to calculate net workdays is to subtract the networkday values associated with your start and end dates.
Big shout out to Imke Feldmann for an elegant solution to a common problem.
- Brian
networkdays.txt (1.9 KB)