
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.
Sign-up now and get instant access
ABOUT THE AUTHOR
Free Trial
On-demand learning
private training
Leave a comment