Using NETWORKDAYS Function in Power BI to Calculate Business Days

Calculating business days is a crucial aspect of project management, finance, and various business operations. The NETWORKDAYS function in Power BI provides an efficient way to compute the number of business days between two dates, excluding weekends and holidays. This powerful function can help streamline your workflow and improve accuracy in your data analysis.

In this article, we’ll explore how to use the NETWORKDAYS function in Power BI effectively. Here's a quick overview of what we'll cover:

Introduction to NETWORKDAYS:

• What is the NETWORKDAYS function?
• Key benefits of using NETWORKDAYS in Power BI.

Setting Up Your Data:

• Preparing your data for calculation.
• Creating a holiday table for accurate business day calculations.

Implementing NETWORKDAYS in Power BI:

• Step-by-step guide to using the NETWORKDAYS function.
• Example scenarios and practical applications.

Tips and Best Practices:

• Handling common challenges and errors.
• Optimizing your DAX formulas for better performance.

By the end of this article, you’ll have a solid understanding of how to leverage the NETWORKDAYS function in Power BI to calculate business days efficiently and accurately, helping you make more informed decisions and streamline your processes.

Implementation in Power BI:

Here are all the steps we will be following in the step-by-step guide to help you set it up:

• About NETWORKDAYS Function
• Grasping the Table Structure
• Calculating the Business Days using NETWORK Function
• Obtaining the Final Output

Happy learning!

1.About NETWORKDAYS Function:

The NETWORKDAYS function in Power BI is used to calculate the number of working days (or business days) between two dates, excluding weekends and optionally, holidays. This function is particularly useful in project management, finance, and other business operations where tracking the duration of work across specific periods is crucial.

The syntax of the NETWORKDAYS function is:

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

. start_date: The start date for the calculation.

• end_date: The end date for the calculation.
• [weekend]: An optional parameter that allows you to specify which days of the week are considered as weekends and are not included in the calculation for working days. If this is not included, then the default value of 1 is used for Saturday and Sunday being the weekend. When using holidays then this parameter must be included.
• [holidays]: An optional parameter that allows you to specify a list of holidays to be excluded from the calculation.

2.Grasping the Table Structure:

The table is named Working_Days.

The table displays data related to order numbers, order dates, and delivery dates. Here’s a breakdown of its contents:

• Order Number: This column lists the unique identifier for each order. The numbers seem to be structured to include the date and a unique suffix, indicating different orders placed on the same day.
• Order Date: This column shows the date on which the order was placed. In this table, all orders were placed on December 23, 2008.
• Delivery Date: This column indicates the date on which each order was delivered. The delivery dates range from December 29, 2008, to January 3, 2009.

This table provides a clear view of the sequence and timing of orders placed and their respective delivery dates.

3. Calculating the Business Days using NETWORK Function:

We will be using the below DAX function to calculate business days.

Business Days =

VAR Order_Date = SELECTEDVALUE(Working_Days[Order Date])

VAR Delivery_Date = SELECTEDVALUE(Working_Days[Delivery Date])

RETURN

NETWORKDAYS(Order_Date, Delivery_Date)

This DAX formula calculates the number of business days (working days) between an order date and a delivery date, excluding weekends and optionally holidays, using the NETWORKDAYS function. Here's a breakdown of the formula:

Variable Declarations:

• Order_Date stores the Order Date column in the Working_Days table.
• Delivery_Date stores the Delivery Date column in the Working_Days table.

Calculation:

• The RETURN statement calculates the number of business days between the Order_Date and Delivery_Date using the NETWORKDAYS function.

Explanation of Each Component:

• VAR Order_Date = SELECTEDVALUE(Working_Days[Order Date]): This line creates a variable called Order_Date that holds the value of the order date from the Working_Days table for the current row or context.
• VAR Delivery_Date = SELECTEDVALUE(Working_Days[Delivery Date]): This line creates a variable called Delivery_Date that holds the value of the delivery date from the Working_Days table for the current row or context.
• RETURN NETWORKDAYS(Order_Date, Delivery_Date): This line returns the number of business days between the Order_Date and Delivery_Date.

How It Works?:

When you use NETWORKDAYS(Order_Date, Delivery_Date), the function will:

1. Count the number of days between Order_Date and Delivery_Date.
2. Exclude Saturdays and Sundays from this count to give you the number of business days.

This is particularly useful for business applications where you need to measure the actual working days taken to complete a task or deliver a product, excluding weekends.

4. Obtaining the Final Output:

After the above calculation we will get this table as the output:

The Business Days column in the above table represents the number of business days between the Order Date and the Delivery Date for each order which we calculated and discussed in the previous step.

Business days typically exclude weekends (Saturdays and Sundays) and can also exclude public holidays if specified.

Download the data for the above from this link.

Download the PBIX file for the above from this link.

Thank you for your attention!

Follow me or subscribe to get all my Power BI articles!

Don’t forget to subscribe to

👉 Power BI Publication

👉 Power BI Newsletter

and join our Power BI community: