How to calculate workdays between two dates in Power BI? #networkdays

DAX function Networkdays is the latest addition to the existing DAX formulas having a crucial requirement as it helps to find the number of working days between two dates considering weekends and holidays in Power BI.

Networkdays function used to exist in excel but was missing in the Power BI DAX formulas list. So it is time to learn how to use networkdays dax function in Power BI.

You need to understand the syntax of the Networkdays DAX function in order to use it in your report development for calculating workdays between two dates.
Syntax: NETWORKDAYS(<start_date>, <end_date>[, <weekend>, <holidays>])
Here, <start_date> and <end_date> are simple to understand but here it is important to understand <weekend> and <holidays>.

In the <weekend> section, you need to enter a whole number representing a single weekend day or weekend combinations such as Saturday and Sunday which we follow mostly all over the world except in Gulf and the Middle East Countries.

In the <weekend> section, you can enter the below-mentioned whole number for the defined weekend days combination.

Weekend number values indicate the following weekend days:
1 or omitted: Saturday, Sunday
2: Sunday, Monday
3: Monday, Tuesday
4: Tuesday, Wednesday
5: Wednesday, Thursday
6: Thursday, Friday
7: Friday, Saturday
11: Sunday only
12: Monday only
13: Tuesday only
14: Wednesday only
15: Thursday only
16: Friday only
17: Saturday only

In the <holidays> section, you will need to enter a column having all the holidays or you can enter a single holiday that lies between the two dates for which we are finding the working days.

How to calculate working days between two dates having weekends on Saturday and Sunday and a holiday in Power BI?

Step 1: Create a Measure

For calculating working days between two dates having weekends on Saturday and Sunday, you will have to create the measure specifying the start and end date, Weekend Number from the above-mentioned list, and holiday column or holiday date should be mentioned.

Number of Working Days =
NETWORKDAYS (
MIN ( financials[Date] ),
MAX ( financials[Date] ),
1,
{ DATE ( 2014, 1, 20 ) }
)

Here, in the above DAX code MIN ( financials[Date] ) is starting date from the starting range taken from the range slicer and MAX ( financials[Date] ) is the end date from the ending range taken from the range slicer, 1 is the weekend number and { DATE ( 2014, 1, 20 ) } is the holiday date given explicitly.

Step 2: Place the measure in Card Visual

Next, you can place the measure created in the card visual and see the result. Here, in this example 22 is the working number of days between 1/1/2014 and 2/1/2014.

Here, 1/1/2014 is the starting date in spite of that starting date in the range slicer is 12/21/2013 because data is not continuous and data is available for 1/1/2014.

How to calculate working days between two dates having weekends on Friday and Saturday and a holiday in Power BI?

Step 1: Create Measure

We just need to change the weekend number from 1 to 7 in order to calculate the number of working days between two dates having the weekend on Friday and Saturday.

number of working days =
NETWORKDAYS (
    MIN ( financials[Date] ),
    MAX ( financials[Date] ),
    7,
    { DATE ( 2014, 1, 20 ) }
)

So here, 7 is the weekend number for the weekend on Friday and Saturday. The rest of the parameters are the same as above.

Step 2: Place the measure in the Card Visual

It is the same as above. Just place the measure in the card visual for display.

Conclusion

With Networkdays it has become really easy to calculate the working days between two dates considering weekends and holidays. Earlier lot of dax needs to be written in order to calculate the working days considering weekends and holidays.