Table of Contents
Calculating the number of working days between two dates in Power BI can be achieved by using the DATEDIFF function, which calculates the difference between two dates. This function can be applied to a column of dates and a specific date, such as the current date, to determine the number of days between them. However, in order to calculate only the working days, the NETWORKDAYS function can be used instead, which excludes weekends and custom holidays from the calculation. By using these functions, the number of working days between two dates in Power BI can be accurately calculated and displayed in reports and dashboards.
Power BI: Calculate Working Days Between Two Dates
You can use the following syntax in DAX to calculate the number of working days between two dates in Power BI:
Working Days Between = NETWORKDAYS(my_data[Start Date], my_data[End Date])
This particular example creates a new column named Working Days Between that contains the number of working days between the date in the Start Date column of a table and the date in the Start End column.
Not that by default, the NETWORKDAYS function assumes that only Monday-Friday are considered working days.
However, you can specify which days of the week should be considered working days and you can create a custom column that specifies which days should be considered holidays and not counted as working days.
Refer to the NETWORKDAYS page for more details.
The following example shows how to use this function in practice.
Example: How to Calculate Working Days Between Two Dates in Power BI
Suppose we have the following table in Power BI named my_data that contains information on the start date and end date for various tasks at some company:
Suppose that we would like to create a new column that contains the number of working days between the dates in the Start Date and End Date columns.
To do so, click the Table tools tab and then click the New column icon:
Then type the following formula into the formula bar:
Working Days Between = NETWORKDAYS(my_data[Start Date], my_data[End Date])
This will create a new column named Working Days Between that contains the number of working days between the dates in the Start Date and End Date columns:
- There are 22 days between 1/1/2024 and 1/30/2024.
- There is 1 working day between 1/5/2024 and 1/5/2024.
- There are 5 working days between 1/15/2024 and 1/19/2024.
And so on.
Additional Resources
The following tutorials explain how to perform other common tasks in Power BI: