How to Calculate NETWORKDAYS in Power BI

Calculating the number of business days between two dates is a fundamental task for tracking project timelines, measuring lead times, or analyzing support ticket resolution. You need a reliable way to exclude weekends and public holidays from your counts. This guide will walk you through exactly how to use the NETWORKDAYS function in Power BI to accurately calculate working days, first for a standard week and then by adding a custom holiday schedule.

What is the NETWORKDAYS Function in Power BI?

In the world of DAX (Data Analysis Expressions), the language used in Power BI, NETWORKDAYS is the function designed specifically for this purpose. It returns the count of whole working days between a start date and an end date. The real power of this function is its ability to not only exclude standard weekends but also a custom list of holidays you provide.

Understanding its syntax is the first step:

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

Let's break down each component:

• <start_date>: The date your period begins. This must be a column with a date data type.
• <end_date>: The date your period ends. This also must be a date column.
• <weekend> (Optional): A number that specifies which days of the week are considered weekends. If you omit this, it defaults to 1, which represents Saturday and Sunday.
• <holidays> (Optional): A table containing a single column of dates that should be treated as non-working days.

The dates in start_date and end_date are inclusive. So, if a project starts and ends on the same working day, the function will return a value of 1.

Step 1: The Basic Working Day Calculation (Weekends Only)

Let's start with the simplest scenario: finding the working days between two dates, excluding only Saturdays and Sundays. Imagine you have a table named 'Projects' with the following data:

'Projects' Table

To calculate the working days for each project, you'll create a new calculated column in your 'Projects' table.

How to Add the Calculated Column

1. Go to the Data View in Power BI and make sure your 'Projects' table is selected.
2. In the Table tools tab of the ribbon, click on 'New column'.
3. In the formula bar that appears, enter the following DAX formula:

Working Days = NETWORKDAYS(Projects[StartDate], Projects[EndDate])

After you press Enter, Power BI calculates the working days for each row. Because we didn't specify the 'weekend' or 'holidays' parameters, the formula defaults to treating Saturday and Sunday as the weekend and doesn't exclude any holidays.

Your table will now look like this:

Step 2: Accounting for Company Holidays

The basic calculation is useful, but most businesses observe public or company-specific holidays. To get a truly accurate count of working days, you need to provide NETWORKDAYS with a list of these dates to exclude.

Create a Dedicated Holiday Table

The best practice is to create a separate table that contains a single column of your company's holidays. This approach keeps your model clean and makes it easy to update holiday schedules year after year.

You can import this from a spreadsheet or create it directly in Power BI.

1. In the 'Home' tab of the ribbon, click 'Enter data'.
2. A dialog will appear. Name the column HolidayDate and name the table Holidays.
3. Enter your holiday dates. Ensure the column is set to the 'Date' data type in Power BI after creation.

Your 'Holidays' table might look like this:

Update the DAX Formula to Include Holidays

Now, let's create a new calculated column to see the difference. You don't need to create a relationship between your 'Projects' table and your new 'Holidays' table in the model view, the DAX function will reference the holiday list directly.

1. Create another new column in the 'Projects' table.
2. Enter this updated formula in the formula bar:

Working Days (Incl Holidays) = 
NETWORKDAYS(
    Projects[StartDate], 
    Projects[EndDate], 
    1, 
    Holidays[HolidayDate]
)

Let’s look at the result. The 'CRM Implementation' project ran from May 20th to June 10th. Its original working day count was 16. However, Memorial Day in the US in 2024 was on May 27th, which falls within that date range. The new formula correctly identifies and excludes this holiday.

Bonus: Handling Non-Standard Weekends

What if your business doesn't follow the traditional Monday-Friday work week? The third parameter of NETWORKDAYS, <weekend>, gives you the flexibility to define which days are off-days.

Here are the common numerical codes you can use:

• 1 or omitted: Saturday, Sunday (Default)
• 2: Sunday, Monday
• 3: Monday, Tuesday
• 4: Tuesday, Wednesday
• 5: Wednesday, Thursday
• 6: Thursday, Friday
• 7: Friday, Saturday
• 11: Sunday only
• 12: Monday only
• ...and so on for single weekend days.

For example, if your company operates in a region where the standard weekend is Friday and Saturday, you would use 7 for the weekend parameter.

The formula would look like this:

Working Days (Fri-Sat Weekend) = 
NETWORKDAYS(
    Projects[StartDate], 
    Projects[EndDate], 
    7, 
    Holidays[HolidayDate]
)

This single change allows you to tailor the working day calculation to your exact business operations, making your analysis far more accurate.

Common Mistakes to Avoid

While NETWORKDAYS is powerful, a few simple mistakes can lead to errors or incorrect results. Keep an eye out for these:

• Incorrect Data Types: All columns used for dates (start, end, and holidays) must have a 'Date' or 'Date/Time' data type in Power BI. If they are stored as Text, the DAX function will fail.
• Blank Dates: If either your StartDate or EndDate is blank, NETWORKDAYS will return a blank value, which is generally the desired behavior.
• End Date is Before Start Date: If an end date accidentally occurs before the start date, the function will return a negative number. This can be useful for data validation, helping you spot errors in your source data.
• Holiday already on a Weekend: If a date in your holiday table falls on a weekend (e.g., a Christmas Day on a Saturday), the function is smart enough not to double-count it. It will be excluded just once as part of the normal weekend count.

Final Thoughts

Learning to use the NETWORKDAYS function is a milestone for any Power BI user. It allows you to transform raw date data into meaningful business metrics like project duration, SLA compliance, and process efficiency. By setting up a dedicated holiday table and understanding the optional parameters, you can build reports that are impressively accurate and specific to your organization.

Writing DAX and managing data models in Power BI is a powerful skill, but it often involves manual setup and a learning curve. At Graphed, we've automated this process. You can connect all your business platforms - like Salesforce, Google Analytics, Shopify, and more - and simply ask questions in plain English. Instead of writing formulas, you could just ask, "What was our average lead-to-close time in business days last quarter?" and get an interactive dashboard in seconds. You can try Graphed to see how we handle the complexities of data analysis so you can focus on the insights.