Calculate Working Days in Power BI

By:   |   Updated: 2021-07-14   |   Comments (7)   |   Related: > Power BI


Problem

Sometimes when we are tasked to compute the number of days in a date range, it might look straight forward initially. But to achieve this in Power BI requires some bit of work as it is not as straight forward as could be done in Excel.

This article describes simple ways to approach this task as it could apply to different business scenarios including calculating total number of days worked by an employee, total number of working days it took for an order to be shipped or delivered, or total number of days for a payment to go through in a particular country and so on.

There are already a few articles and YouTube videos demonstrating how to achieve this in Power BI using different approaches, but this tutorial looks to apply a simplified approach.

Solution

To calculate working days in Power BI requires that we should know which days are weekends and, which days are Bank holidays (Public Holidays \ Federal Holidays - Christmas Day, New Year's Day, Labor Day, Independence Day, Veterans Day, Columbus Day, Memorial Day, etc.) so we can exclude these from the total number of working days.

Looking at the sample dataset below, we can see we only have a start and end date. We need to compute the days within each date range that are not working days and exclude these days from the total workdays we are calculating. We can determine how many days the work was done (assuming the workers are not working on weekends and holidays), in order to build our working days calculator.

Table with Start date and End date

In Excel this can be easily achieved by using the WORKDAY or NETWORKDAYS function with the given dates, which eliminates both weekends and holidays. But to do this in Power BI, we need to build our own business days calculator following these steps:

  1. Prerequisite should include a date range
  2. Create DAX logic to generate a column that calculates Total Workdays excluding weekends
  3. Create DAX logic that calculates Total Workdays excluding public holidays
  4. Verify the final output using filters in the DatesTable

STEP 1: Prerequisite should include a date range

It should be noted that for this approach to work there should be either a From and To or Starting Date and Ending Date or some dates columns that have a date range to compare (including having to compare it with TODAY). You will see why this is important later as we calculate the number of workdays.

STEP 2: Create DAX logic to generate a column that calculates Total Workdays excluding weekends

To make this easy to create I have included DAX code you can copy and adapt in your solution to calculate the total number of working days excluding weekend days only. Please note that holidays might still be included in this calculation. We will address the holiday dates shortly.

The DAX code is as seen below. You need to create a calculated column in the table where you have the date range (in this case we have the date range as StartDate and EndDate).

Workdays = 
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            CALENDAR ( 'Sheet1 (2)'[StartDate], 'Sheet1 (2)'[EndDate] ),
            "Day of Week", WEEKDAY ( [Date], 2 )
        ),
        [Day of Week] <> 6
            && [Day of Week] <> 7
    )
)

The above code can also be seen in the DAX formular window of Power BI as seen below.

DAX workdays excluding weekends

Without going into the details of the DAX code, what the DAX logic is doing at the back end is that it creates a Calendar table with a dates column ranging from the StartDate to the EndDate for each row, then it computes and adds another column (Day of Week) which uses the WEEKDAY DAX function to determine if the dates in the date range for each row in the table is a weekday or not. If the dates are weekend dates, the FILTER function ensures they are not included. Then, the COUNTROWS function counts the number of rows in the date range for each row in the calculated Calendar table that meets the filter condition, this is what we see in the output in the table as seen below.

Table with Start date and End date and Total workdays excluding weekends columns

STEP 3: Create DAX logic that calculates Total Workdays excluding public holidays

It is easier to compute the total days excluding weekends as compared to doing the same for holidays. There is no such function in DAX that can assist with this, unlike the WEEKDAY used earlier for figuring out and excluding weekends from our calculation.

So, to calculate total working days excluding holidays we need to be a little more creative in Power BI. I have detailed out a series of sub steps you can follow to achieve this easily.

  1. Create a Holiday dates table: This table should hold Holiday dates, Holiday Reasons and if possible, Weekdays. It is possible to get holiday dates by searching "Bank holidays in the UK" for example. You can do the same for the country or countries where you are working. I am in the UK and I have generated a list of all Bank holidays in England and Wales from the GOV.UK site. You might need to do some minor data cleansing in Power Query to get this table in shape and combine all the holiday dates for each year from 2016 to 2022. See the output Holiday table below.
UK holidays dates table
  1. Create a Calendar dates table: This is going to be used to connect a relationship between the other tables as shown later. And the Calendar dates table would also serve as a proof checking table as it will be demonstrated later. To create this table, we will be using the DAX expression below.
DatesTable = CALENDAR(MIN('Sheet1 (2)'[StartDate]), MAX('Sheet1 (2)'[EndDate]))
  • We can add a column (IsWorkingDay) to denote which of the dates are Weekdays or not.
IsWorkingDay = NOT WEEKDAY( 'DatesTable'[Date]) IN { 1,7 }
  • Next, we can add a WeekDay column to show the day of the week for each date in the table. The DAX expression below would add a calculated column to the DatesTable.
WeekDay = FORMAT(DatesTable[Date], "dddd")
  1. Create a relationship between the three tables: Since we now have three tables (Sheet1 (2), DatesTable, and Holiday Table), we need to create a relationship between them as shown in the diagram below.
Relationship model diagram for creating Total workdays excluding holidays and weekends
  1. Add the Holiday Reason column from the Holiday Table to the DatesTable: As we would need to know which dates are Bank Holidays, we need to add this column to the DatesTable for verification using the DAX expression below.
Holiday Reason = RELATED('Holiday Table'[Holiday Reason])
  • The output of the DatesTable is as shown below. It shows that we can use filters on this table to check later if our calculations are accurate i.e. are the dates matching the Holiday Reasons, etc.
Dates table showing IsWorking, weekday and Holiday reason columns
  1. Create the DAX calculated column to compute the Total Working Days excluding Holidays only: At this point, the heavy work has been done, we now only require a single DAX expression to remove holidays from the Working days calculation. The DAX expression is as seen below.
Workdays 2 (After excluding Holidays only) = 
    COUNTROWS ( 
        FILTER ( 
            ADDCOLUMNS( 
                CALENDAR ('Sheet1 (2)'[StartDate], 'Sheet1 (2)'[EndDate]), 
              "Is Holiday", CONTAINS ('Holiday Table', 'Holiday Table'[Dates], [Date]) 
            ), 
            [Is Holiday] = FALSE() 
        ) 
    ) 
  • The above code can also be seen in the DAX formular window of Power BI as seen below.
DAX workdays excluding holidays
  • Without going into the details of the DAX code, what the DAX logic is doing at the back end is that it creates a Calendar table with a dates column ranging from the StartDate to the EndDate for each row, then it computes and adds another column (Is Holiday) which uses the CONTAINS DAX function to determine if the dates in the date range for each row in the table is an holiday or not by returning TRUE or FALSE. If the dates are holiday dates, the FILTER function ensures they are not included. Then, the COUNTROWS function counts the number of rows in the date range for each row in the calculated Calendar table that meets the filter condition, this is what we see in the output in the table as seen below.
Table with Start date and End date and Total workdays excluding holidays columns
  • With this we have successfully calculated the number of working days columns for weekends excluded and holidays excluded individually. But the end solution would need a calculation that excludes both Weekend dates and Holiday dates from the total working days calculated per row.
  • This is now very straight-forward, all we need to do is to adjust the last DAX expression a bit as seen below.
Workdays 3 (After excluding Weekends & Holidays) = 
    COUNTROWS ( 
        FILTER ( 
            ADDCOLUMNS(
                CALENDAR ('Sheet1 (2)'[StartDate], 'Sheet1 (2)'[EndDate]),
              "Is Weekday", WEEKDAY ([Date], 2) < 6,
              "Is Holiday", CONTAINS ('Holiday Table', 'Holiday Table'[Dates], [Date])
            ),
            [Is Weekday] = TRUE() && 
            [Is Holiday] = FALSE()
        )
    )
  • The above code can also be seen in the DAX formular window of Power BI as seen below.
DAX workdays excluding weekends and holidays
  • Without going into the details of the DAX code above, what the DAX logic is doing at the back end is that it creates a Calendar table with a dates column ranging from the StartDate to the EndDate for each row, then it computes and adds two other columns (Is Weekday and Is Holiday). It uses the WEEKDAY DAX function to determine if the dates in the date range for each row in the table is a weekday or not and only including those that are weekdays by retuning weekday numbers below 6 based on the WEEKDAY syntax. Likewise, at the same time it uses the CONTAINS DAX function to determine if the dates in the date range for each row in the table is an holiday or not by returning TRUE or FALSE. The FILTER function ensures only where the condition of where there is weekdays and where there is not holidays is returned. Then, the COUNTROWS function counts the number of rows in the date range for each row in the calculated Calendar table that meets the filter condition, this is what we see in the output in the table as seen below.
Table with Start date and End date and Total workdays excluding holidays and weekends columns

STEP 4: Verify the final output using filters in the DatesTable

To verify we have 285 working days spent on Fencing, we can check in the DatesTable using filters to only show rows of dates between "13/04/2020" and "29/05/2021". This is demonstrated in the diagrams below.

How to navigate to filter a table using the between option

The above diagram shows how you can navigate to create date ranges to verify the total number of working days between any of the dates in your dimension or fact table which in this case is represented by the "Sheet1 (2)".

How to navigate to filter a table using the between option 2

The above table shows the date range we are verifying entered in the filter window.

Next, we do the same filter for the "IsWorkingDay" column to ensure we don't include weekends as shown in the diagram below.

How to filter a column for a single value

Next, we do the same filter for the "Holiday Reason" column to exclude all holidays and only include Blank rows, indicating dates that are not holidays as seen in the diagram below.

How to filter a column for a single value 2

The total count of rows after all the applied filters above can be seen at the bottom of the page as shown in the diagram below.

Power BI table inbuilt capability showing total filtered rows

And it can be seen to be the same as the 285 we calculated earlier. You can repeat this for any of the date ranges, you should get exact same values for verification purposes.

Summary

In summary, in this tutorial, we have successfully demonstrated how to calculate working days in Power BI by excluding weekends and Bank Holidays. I have also demonstrated ways to verify you are getting accurate values in the end. Although, this calculation is relatively straight-forward in Excel, its clear we needed to be a bit creative in Power BI to achieve the desired solution. You can apply this approach in your own solutions by following each step carefully as each step has its own part in the puzzle of the solution.

Next Steps
  • To understand some common data cleaning in Power BI see my article on this here.
  • Checkout this article by RADACAD more details about the WEEKDAY function here.
  • Get some more on all you need to know about Date Tables in Power BI by SQLBI here.
  • Try this tutorial out in your own data as business requires.





get scripts

next tip button



About the author
MSSQLTips author Kenneth A. Omorodion Kenneth A. Omorodion is a Microsoft Certified Data Analytics and BI Professional mostly in Microsoft BI stack of tools.

View all my tips


Article Last Updated: 2021-07-14

Comments For This Article




Thursday, September 22, 2022 - 10:03:51 AM - Aigbe Kenneth Omorodion Back To Top (90513)
Hi Barry, if i understand your requirement properly, i would suggest you create an additional column (similar to the one we did for "IsWorkingDay") to identify which rows have errors or blanks and exclude them in the DAX as we did for the IsWorkingDay column. You would need to be a bit more creative in this.

Thursday, September 22, 2022 - 8:17:47 AM - Barry Back To Top (90511)
This solution seems to be what I need but in testing it I have 1 significant issue that I am uncertain how to mitigate. When calculating the workdays column that script ERROERS out if there are blank spaces in the 'start-date' which in our case is feasible as the project has not started yet.

How can this be corrected without creating another table of only work that has been started?

Friday, July 15, 2022 - 4:54:04 PM - Praveen Arora Back To Top (90266)
Solution worked perfectly well.. Thanks for detailed solution approach.

Thursday, March 17, 2022 - 2:51:47 PM - Aigbe Kenneth Omorodion Back To Top (89899)
Hi Alejandro,

I would like to see a sample of your dataset to understand more, at least provide more details so i see if i can help with that. Can i suggest that you could consider applying a logic where you create a new column to pick the highest between the End date or Due date to use as "End Date" and the earliest as "Start Date". nWould that work for your business requirement, or provide more details and i will look into it for you.

Thanks.

Monday, March 14, 2022 - 8:24:59 AM - Alejandro Back To Top (89872)
Good morning,

I'm looking to apply a very similar calculation in PowerBi, but your approach seems not to address it 100%. In our case, we have a "Date End" and a "Date Due". We want to measure deviations between the end of a task and the due date of that task. Sometimes, the task is ended before it was due, but it's also possible to see the contrary scenario, where the task is ended after it's due. When trying to create the calculated column "Workdays" using DAX, it indicates that the "The start date in Calendar function can not be later than the end date."

Do you know how to workaround this?

Thanks a lot in advance!

Wednesday, December 8, 2021 - 11:21:26 AM - Aigbe Kenneth Omorodion Back To Top (89550)
Hi Satya,

Its great to hear that you gained much from this article.

As per your business requirements, although its not so clear as to the details yet, but i think i understand the aspect that each state in Australia has its own holidays (which might be different from the other states). What i would do in that instance is to create a seperate holiday table for each state and connect them with the DatesTable (CalendarTable) and the logic from there would follow same as was done as a single holiday table.

If you can provide more details clearly i migh be able to suggest best approach on what you can try, or i can even expand this article with a sample of your business scenario.

Thanks.

Tuesday, December 7, 2021 - 4:29:31 PM - satya sunkara Back To Top (89542)
This is a very good tip. Still our satiation does not end here. We wish in places like Australia, the holidays specific to the state they live or data coming. Each state has their battery of holidays. If we can pass state filter along the holiday give us more accurate results.
Additional satiation is the date time is in local time to be converted to EST before calculating. More... We have to have a cut off office hours, extra out of hours to be sandwiched with accuracy of winter/ summer solstice windows (like in UK/Australia). Now can DAX delay out or seamless still on the fly or flywheel.














get free sql tips
agree to terms