Excel has a NETWORKDAYS function that calculates the number of days between two dates minus weekends and holidays. While DAX has a DATEDIFF function that calculates the number of days between two dates, the DATEDIFF function does not account for subtracting weekends and holidays. This recipe is a recreation of Excel's NETWORKDAYS function in DAX.

To prepare for this recipe, perform the following steps:

1. Use an Enter Data query to create a table called R10_Table with the following columns and rows:

2. Ensure that both columns are set to have a data type of Date.
3. Create the following column in the R10_Table table:

Number of Days = DATEDIFF([Created Dated],[Finished Date],DAY)

4. Place all three columns from the R10_Table table in a Table visualization on a Report page.
5. Set the Created Date and Finished Date fields in this Table visualization to be their actual dates, not their date hierarchies.

To implement this recipe, perform the following steps:

1. Create a new measure with the following formula:

NetWorkDays = 
    VAR __Date1 = MAX('R10_Table'[Created Date])
    VAR __Date2 = MAX('R10_Table'[Finished Date])
    VAR __Date1a = MINX( { __Date1, __Date2 },[Value])
    VAR __Date2a = MAXX( { __Date1, __Date2 },[Value])
    VAR __Calendar = 
        ADDCOLUMNS(
            CALENDAR(__Date1a, __Date2a),
            "__WeekDay",
            WEEKDAY([Date],2)
        )
RETURN
    COUNTX(
        FILTER(
            __Calendar,
            [__WeekDay] < 6
        ),
        [Date]
    )

2. Place the NetWorkDays measure in the Table visualization created earlier.

This recipe is fairly straightforward. The first four lines simply ensure that we construct our calendar table in such a way that the earliest date comes first. We then construct a calendar table, __Calendar, that holds our two dates as well as all of the dates between those two dates. We add a column, __WeekDay, which stores the number of the week day (1-7). By using the 2 as our second parameter for the WEEKDAY function, Saturday = 6 and Sunday = 7. We then simply need to filter this table where the __WeekDay column is not 6 or 7 and return the count of the days (rows) remaining.

We can modify our NetWorkDays calculation to account for holidays as well. To do this, observe the following steps:

1. Create the following table using an Enter Data query:

2. Ensure that the Date column is flagged as having a data type of Date.
3. Create a new measure with the following formula:

NetWorkDays2 = 
    VAR __Date1 = MAX('R10_Table'[Created Date])
    VAR __Date2 = MAX('R10_Table'[Finished Date])
    VAR __Date1a = MINX( { __Date1, __Date2 },[Value])
    VAR __Date2a = MAXX( { __Date1, __Date2 },[Value])
    VAR __Calendar = 
        ADDCOLUMNS(
            EXCEPT(
                CALENDAR(__Date1a, __Date2a),
                'R10_Holidays'
            ),
            "__WeekDay",
            WEEKDAY([Date],2)
        )
RETURN 
    COUNTX(
        FILTER(
            __Calendar,
            [__WeekDay] < 6
        ),
        [Date]
    )

4. Place this NetWorkDays2 measure in the Table visualization created earlier.

The small change to the formula to account for holidays is to use the EXCEPT function to remove the dates in the R10_Holidays table from the calendar table that we create, __Calendar.

For more details regarding the functions in this recipe, refer to the following links:

• CALENDAR: https://docs.microsoft.com/en-us/dax/calendar-function-dax
• WEEKDAY: https://docs.microsoft.com/en-us/dax/weekday-function-dax
• FILTER: https://docs.microsoft.com/en-us/dax/filter-function-dax
• COUNTX: https://docs.microsoft.com/en-us/dax/countx-function-dax
• EARLIER: https://docs.microsoft.com/en-us/dax/earlier-function-dax