Calculating Work Days for Power BI Reports using NETWORKDAYS Function

By:   |   Updated: 2022-08-16   |   Comments   |   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





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: 2022-08-16

Comments For This Article

















get free sql tips
agree to terms