Calculate Workdays Between Two Dates In Power BI

I’m going to show you how to calculate workdays / working days between two dates either with the holidays counted or without the holidays. This is a topic that’s been asked over and over again on the Enterprise DNA Forum.

I also believe that this will be a great way to close this Time Intelligence series, knowing that it covers a lot of the other elements discussed earlier on. These elements include creating a holiday table, creating an extended date table, and using the DATESBETWEEN function, among others. You may watch the full video of this tutorial at the bottom of this blog.

Table of Contents

Ways To Calculate Workdays Between Two Dates

I’m going to talk about different approaches you can use to calculate workdays.

Excel allows users to calculate workdays in a straightforward manner through the use of its NETWORKDAYS function. However, DAX does not have a function like that, so I’m going to discuss ways to get around this.

One way to do this is by using some elements from the extended date table.

Another option is through an approach that I got from Imke Feldmann from thebiccountant.com blog. She created a custom NetworkDays function that can be invoked straight from the Power Query, making the process much faster and less complicated.

To show you how these approaches work, I’m going to use a very common scenario.

I have one hundred projects that I’ve anonymised.

calculate workdays, power bi working days screenshot 1

I also have a Start Date and an End Date for each project.

calculate workdays, calculate working days in power bi screenshot 1

I have created a simple Days Elapsed measure as well. This is basically the raw number of days between each of the start and end dates. I used the DATEDIFF function to do this, a useful function to datediff working days.

The data model is very simple. It contains tables for Dates and Projects.

calculate workdays, power bi networkdays screenshot 1

It also shows the fact table, which includes the Project ID, Start Date and End Date.

For comparison purposes, I also calculated the NetworkDays (with and without holidays) using Excel (not power bi).

Of course, you can do the same thing and bring in data from Excel. But the goal here is to use Power BI to calculate the number of workdays without having to use Excel.

Calculating Workdays Between Dates Using DAX

Anytime you deal with counting dates, it’s always efficient to use the COUNTROWS function on the Dates table.

I also used DATESBETWEEN to narrow down the field between the Start Date and End Date. I also added IsWorkingDay to apply the simple Boolean logic that if it’s a workday then it should be TRUE. Weekends would return a FALSE.

I finished that off with the ALLSELECTED filter just to take the context of slicers into account.

As for the version with holidays, it’s very similar to the measure I’ve just discussed.

It’s the same expression using the DATESBETWEEN function. I only added a FILTER and IsHoliday.

Again, I used the ALLSELECTED function at the very end.

See what happens when I drag those two measures into my table.

As expected, the Workdays w/ Holidays perfectly match the Excel w/ Holidays column. The same thing goes for the Workdays w/o Holidays column, which matches the Excel w/o Holidays column.

Calculating Workdays Using The Power Query

The approach that Imke Feldmann from thebiccoountant.com blog talks about gives an even easier solution when using the Power Query.

In her blog about the NETWORKDAYS function, she gave an M code that can be invoked in the Power Query.

Simply copy the M code from her blog.

Then, go into the Power Query and under New Source, open up a Blank Query.

Go into the Advanced Editor.

Then paste the entire code into the page. Make sure you have a green check to make sure there are no errors in your code.

Once it is invoked, it will bring you to this template.

It doesn’t really matter what I enter here, so I’ll just go with January 1st 2018 to January 1st 2020.

This will now show up under my queries. I’m going to rename that to make it easier for us to use later on.

I’ll name it Networkdays.

Now, I can start using that query. I’ll just go into my Data.

Then, under Add Column, I’ll click on Invoke Custom Function.

I’m going to call this NWD and use the Networkdays function.

Then, I’ll choose Start Date and End Date from these dropdown menus below.

It gives me the option here to change the start of week from Monday to something else.

For this purpose, Monday is fine, so I’ll leave that as it is. Then, I’ll click on OK.

As you can see, it immediately calculates the NetworkDays equivalent to the Excel NetworkDays column.

Now, let me tweak this so I can exclude the holidays. I just need to go into my Holiday table.

If you recall from the template, the Holiday table is the third parameter here.

So I’ll just use the Holidays table here and reference the Date column.

I’ll click the checkmark here to apply those changes.

[00:11:00]
Now, it’s been updated and has removed all the holidays. It’s exactly the same as the column for Excel NetworkDays with no holidays.

I’ll just rename this column NWD No holiday.

Conclusion

You’ll see that both approaches I used were very different from each other, but they both yielded the same results. This only shows that there really are are lot of ways to maximize Power BI, even when the specific Excel function you’re looking for has no direct counterpart.

This has been a great run for the Time Intelligence series. I enjoyed working with Melissa Dekorte to show you all these great Time Intelligence scenarios.

One of the things I really want to emphasize here is that DAX is the most interesting part of Power BI. But as you master the ins and outs of Power BI, you’ll start to appreciate other solutions that do not involve DAX at all. The last approach I showed you above, as well as the tips I shared in the past videos on Time Intelligence prove this fact.

All the best,

Brian