Calculating Work Days for Power BI Reports using NETWORKDAYS Function

By: Kenneth A. Omorodion   |   Updated: 2022-08-16   |   Comments (4)   |   Related: > Power BI

Problem

I wrote an article on how to calculate working days in Power BI, which employed an approach based on a combination of data modeling and DAX. The approach was very effective but lengthy since there was no pre-defined function in Power BI DAX to do so at the time.

The Power BI team at Microsoft recently released a new DAX function known as NETWORKDAYS, which is very similar to how Excel functions NETWORKDAYS.INTL and NETWORKDAYS work. This has opened a new way to approach the same business requirements but in a more simplified manner.

Let's look at how this can be done.

Solution

For this demo, I am going to use the same business scenario I used in my other article on calculating working days in Power BI. I would recommend that you also read that article when possible so you can fully understand how to approach any scenario with techniques in these articles.

How Does the NETWORKDAYS Function Work?

First, let's briefly discuss the NETWORKDAYS DAX function.

The NETWORKDAYS DAX function will return an integer number of whole workdays between two dates (Start and End dates, Beginning and Last dates, etc.). The function has four parameters (two mandatory and two optional), as shown below.

NETWORKDAYS(<start_date>, <end_date>[, <weekend>, <holidays>])

The <start_date> and <end_date> are mandatory. The <weekend> and <holidays> are optional. However, once you declare the <holidays> parameter, the <weekend> parameter becomes mandatory to declare, too, as we will discuss later in this tip. To get more information on how the mandatory and optional parameters work, please refer to the Microsoft documentation about NETWORKDAYS.

How to Use NEWORKDAYS Function to Solve Business Problems

Calculating working days requires excluding weekends and bank holidays (holidays) from the overall working days. See the dataset below to demonstrate a typical business scenario.

The dataset above only has a Startdate and EndDate, which are okay for the mandatory parameters of the NETWORKDAYS function. However, to get the accurate number of days to complete each type of work in the description column, we need to exclude weekends and holidays from the date ranges.

To do this, follow these steps:

1. Create a separate holidays table
2. Determine what days are weekend days
3. Create a column for total working days
4. Create a measure for total working days

STEP 1: Create a Separate Holidays Table

For this demo, I have extracted UK bank holidays from the Gov.UK site. In your case, this may already be defined, but ensure it is a table with a single column of dates. See the diagram below.

If there is more than one column on the holidays table extracted from the source, use the SUMMARIZECOLUMNS DAX function to create the single-column holiday table, as seen in the DAX expression below.

HolidayTable = SUMMARIZECOLUMNS('Holiday Table'[Dates])

STEP 2: Determine What Days are Weekend Days

This step will depend on your organization, solution requirements, or business rules. In this demo, let's assume weekend days include Saturday and Sunday. So, we'll use "1" as the weekend (3^rd argument). You can read more on the different ways to represent the weekend parameter here.

STEP 3: Create a Column for Total Working Days

Creating a calculated column and a measure using this function is different. To create a calculated column, we use the default syntax mentioned earlier, as seen below.

Working Days (Column) = NETWORKDAYS('Sheet1 (2)'[StartDate], 'Sheet1 (2)'[EndDate], 1, 'Holiday Table (2)')

The output of the code above is shown in the below table.

Note: The single-column holiday table (Holiday Table (2)) has not been connected to the main table (Sheet1 (2)) in the model, as seen below. This still works.

STEP 4: Create a Measure for Total Working Days

As mentioned earlier, creating a measure using the NETWORKDAYS DAX function is different from how we do the same for Calculated columns. To create a measure, it's best to leverage variables, as seen below.

Working Days (Measure) = 
        VAR _StartDate = SELECTEDVALUE('Sheet1 (2)'[StartDate])
        VAR _EndDate = SELECTEDVALUE('Sheet1 (2)'[EndDate])
    
    RETURN
        NETWORKDAYS(_StartDate, _EndDate, 1, 'Holiday Table (2)')

To demo the output of the measure, a slicer was created using the "Description" column of the dataset to produce a card visual from the measure created, as seen below.

When the fencing is selected on the slicer, it renders 294 on the card visual. Similarly, when any other description on the slicer is selected, it renders the total number of working days, excluding weekends and holidays, which was the case when we created a Calculated column.

In summary, if you have read my other article on "Calculating working days in Power BI" which did not leverage the NETWORKDAYS function you would agree that this new article leveraging the function is much simpler to implement, takes less time, and is less complex.

Next Steps

• Read my other article on calculating working days in Power BI here.
• Check out the Microsoft documentation on NETWORKDAYS DAX function here.
• You can get more information on DAX NETWORKDAYS function on the DAX Guide site here.
• Read more about the SELECTEDVALUE DAX function here.
• Try this tip with your data as your 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