Calculating Work Days for Power BI Reports using NETWORKDAYS Function

By:   |   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.

Table with Start date and End date&#xA;

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.

Single column of dates table&#xA;

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

How to calculate a column of working days using NETWORKDAYS DAX

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.

Data model for calculating working days in Power BI

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.

How to calculate a measure of working days using NETWORKDAYS DAX

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

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


Click here to start the Power BI course






sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Kenneth A. Omorodion 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


Article Last Updated: 2022-08-16

Comments For This Article




Sunday, June 25, 2023 - 2:19:19 AM - Harry Back To Top (91338)
In some cases the date fields could be empty, for instance, if somebody didn't complete a date. How would you resolve this? at the moment the function returns a large number

Monday, June 5, 2023 - 3:25:20 PM - Wolf Back To Top (91252)
Hi,
I had to deal with empty "end date" records. In that case, a duration is calculated.
Meanwhile I found a solution to calculate the networkdays only if both dates are populated.

Tuesday, May 30, 2023 - 10:06:49 AM - Aigbe Kenneth Omorodion Back To Top (91237)
Hi Wolf,

Thanks for taking time to read my article and feedback on it.

Relating to your question, i dont want to assume i fully understand it yet, please see if you can provide more details please.

Thanks,

Ken.

Tuesday, May 30, 2023 - 9:38:19 AM - Wolf Back To Top (91236)
Thanks for this tutorial !

A question, though: how to deal with empty dates?














get free sql tips
agree to terms