Calculate working days in DAX

Recently Microsoft announced a new DAX function to calculate working days, meaning days between 2 dates, ignoring weekends and (optionally) holidays.


example:

count_workdays = 
NETWORKDAYS(
    DATE(2022,10,1), //start date
    DATE(2022,10,31), //end date
    7 
)

Counts the days between the start date and end date, ignoring weekend days. The weekend days are defined by an indicator, you can find the full list of options in the documentation, but the most relevant options are:

7 = Friday and Saturday (in Israel)

8 = Saturday and Sunday (everywhere else)


If we want to ignore holidays, we can add a list of holiday days:

count_workdays_include_holidays = 
NETWORKDAYS(
    DATE(2022,10,1),
    DATE(2022,10,31),
    7,
    {
        DATE(2022,10,4),
        DATE(2022,10,5)
    }
)

Or, much better, create a small holiday table to hold the holiday dates:

Under home, click on "enter data", to create a manual table.


And now our DAX goes like this:

count_workdays_include_holidays = 
NETWORKDAYS(
    DATE(2022,10,1),
    DATE(2022,10,31),
    7,
    VALUES(Holidays[Holiday_Date])
)

and our results:



0 comments

Recent Posts

See All

STAY IN TOUCH

Get New posts delivered straight to your inbox

Thank you for subscribing!