Return Working Days in Power BI using DAX

In this video , Mitchell Pearson explores how to calculate the number of working days from a date table in Power BI. He demonstrates how to count working days from a date table, including the option to consider basic holidays.

Basic Calculation

 Mitchell explains a basic approach: counting rows in the date table to get the total number of days. This method assumes a well-structured date table.

Network Days Function

Mitchell then introduces the "Network Days" function, which offers a more straightforward way to calculate working days and handle holidays. This function requires a start date and an end date, with optional parameters for specifying weekends and holidays.

Working Days Calculation

Mitchell demonstrates the creation of a new measure called "Working Days" using the "Network Days" function.

•  Two variables, "first date" and "last date," are defined based on the current filter context.
• The "Network Days" function is used with these variables to calculate the working days, excluding weekends by default.

Weekend Parameter

Mitchell explores the optional "weekend" parameter, allowing customization of which days are considered weekends. By changing this parameter, users can specify different weekend days (e.g., excluding only Sundays).

Handling Holidays

Mitchell briefly discusses how to handle holidays using the "Network Days" function.
He suggests creating a list of holiday dates to pass as an optional parameter to exclude these dates from the calculation. However, it's more efficient to have a dedicated "is holiday" column in the date table.

Conclusion

Mitchell refers to Microsoft Learn documentation to provide additional insights and details about the "Network Days" function. The video demonstrates that the "Network Days" function simplifies the process of calculating working days and can handle different scenarios involving weekends and holidays.

Overall, this video tutorial explains how to use DAX functions in Power BI to calculate working days efficiently, providing flexibility in defining weekends and handling holidays.