If you're a frequent user of Microsoft Excel or Power BI, then you may already be familiar with the DAX (Data Analysis Expressions) language. DAX is a formula language used to create custom calculations in Power BI and Excel, and it's widely used by business analysts and data professionals. Recently, Microsoft has added a new function to the DAX language called NETWORKDAYS, which can be incredibly useful for anyone who needs to calculate working days.
The NETWORKDAYS function in DAX calculates the number of working days between two dates, excluding weekends and holidays. This can be particularly helpful for calculating project timelines, forecasting delivery dates, or calculating the number of days until a deadline.
How to Use Syntax
The syntax for the NETWORKDAYS function is straightforward. It takes three arguments: the start date, the end date, and an optional list of holidays. Here's an example:
NETWORKDAYS( start_date, end_date, [holidays]
The start_date and end_date arguments are required, and they should be entered as date values. The [holidays] argument is optional, and it allows you to specify a range of dates that should be treated as holidays. You can enter this argument as a list of dates or as a reference to a range of cells that contain holiday dates.
See NETWORKDAYS In Action
Here's an example that uses the NETWORKDAYS function to calculate the number of working days between two dates, excluding weekends and a holiday:
NETWORKDAYS( "3/1/2023", "3/31/2023", { "3/17/2023" } )
In this example, the function calculates the number of working days between March 1, 2023, and March 31, 2023, excluding the holiday on March 17, 2023. The result of this function is 22, which is the number of working days between these two dates.
One of the great things about the NETWORKDAYS function is that it's highly customizable. You can use it to exclude any day of the week, not just weekends. For example, if you want to exclude Fridays from your calculations, you can use the NETWORKDAYS.INTL function instead of the standard NETWORKDAYS function. This function allows you to specify which days of the week should be considered weekends.
Conclusion
Using NETWORKDAYS can streamline your day calculations without the need for multiple filters and variables in your DAX code. Let DAX do the hard work for you in the background while you reap the benefits.
The new NETWORKDAYS function in DAX is a powerful tool that can save you time and effort when it comes to calculating working days. Whether you're working on a project timeline or trying to forecast delivery dates, this function can help you get accurate results quickly and easily. So if you're a DAX user, be sure to give the NETWORKDAYS function a try!
If you have any questions, or are looking to learn more about different features in Power BI, contact JourneyTEAM today!