Calculating Workdays Between Two Dates in Power BI

Calculate Workdays Between Two Dates In Power BI

3 comments

I’m going to show you how to calculate workdays 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.

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

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

calculate workdays

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.

calculate workdays

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

calculate workdays

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

calculate workdays

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

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.

calculate workdays

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.

calculate workdays

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

Go into the Advanced Editor.

calculate workdays

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.

calculate workdays

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.

calculate workdays

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.

calculate workdays

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

calculate workdays

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

***** Related Links *****
Adding Workday And Weekend Numbers Into Your Date Table: Time Intelligence Technique In Power BI
Showcasing Workday Number in Power BI Using DAX
Calculating Average in Power BI: Isolating Weekday or Weekend Results Using DAX

***** Related Course Modules *****
Time Intelligence Calculations
Mastering DAX Calculations
Power Query Series

***** Related Support Forum Posts *****
Moving Average Issue – Workdays Only
Number Of Working Days
DateDiff To Only Calculate Number Of Work Days
For more calculate workdays support queries to review see hereā€¦

3 comments on “Calculate Workdays Between Two Dates In Power BI”

  1. Great Brian. Thanks so much. This step by step approach showing the DAX as you build up the final measure is really helpful. Thanks for taking the trouble to document and of course, share you knowledge! Erica

  2. Interesting stuff. I’m pretty new to BI so just wondering, when selecting your Holiday table using Imke Feldmann’s code, is it possible to have this vary depending on certain criteria (e.g. using an if formula to look at department and return different tables based on this)?

    1. Hi Logan – Thanks for your interest in Enterprise DNA Blogs.

      Yes that is very much possible. Imke Code is basically a PQ function that can be invoked from any other query including IF condition. Create a new query and in IF condition call the Function by passing different parameters based on requirement.

      For more information on Power Query and how can it be extended, refer to below EDNA courses.

      https://portal.enterprisedna.co/courses/enrolled/1412764
      https://portal.enterprisedna.co/courses/enrolled/197328

      Please feel free to ask any other query related to this Blog Post.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.