Calculate Working Days in Power BI

By: Kenneth A. Omorodion   |   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.

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.

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.

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.

2. 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")

3. 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.

4. 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.

5. 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.

• 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.

• 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.

• 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.

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.

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)".

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.

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.

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.

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.

Learn more about Power BI in this 3 hour training course.

About the author

Kenneth A. Omorodion is a Business Intelligence Developer with over eight years of experience. He holds both a bachelor’s and master’s degree (Middlesex University in London). Kenneth has the MCSA, Microsoft Data Analyst - Power BI and Azure Fundamentals certifications. Kenneth is a Microsoft Certified Trainer and has delivered corporate training on Power BI, SQL Server, Excel and SSRS.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips