NETWORKDAYS() Holiday question

[olimilo]

Recently discovered the NETWORKDAYS() function, I have a question regarding the usage of the Holidays parameter of the function. We have our holiday table setup like so:

I am getting the following error when injecting the holidays into the NETWORKDAYS() formula: "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

FieldTAT = 
    VAR vStartDate = 'WF'[InspectionDate]
    VAR vEndDate = MAX('WF'[ReportReceivedDate], TODAY())
    VAR vHolidays =
        FILTER(
            'Holidays',
            'Holidays'[Country] = 'WF'[Country]
            && 'Holidays'[Date] >= vStartDate && 'Holidays'[Date] <= vEndDate
        )

    RETURN
    SWITCH(
        TRUE(),
        'WF'[WeekendType] = 3, NETWORKDAYS(vStartDate, vEndDate, 6, vHolidays), // THU/FRI
        ...
        NETWORKDAYS(vStartDate, vEndDate, vHolidays)
    )

My error is obviously with how I am trying to use the holiday table here so is there a way to fix this?

Another question is, is there a way to add a custom weekend identifier? According to the KB, there is no weekend identifier for Fri+Sun as the weekend. We are currently calculating our TAT using a custom calendar that is not the most optimized solution:

VAR vCalendar = CALENDAR(MIN(vStartDate, vEndDate), MAX(vStartDate, vEndDate))
    VAR vWeekdays = ADDCOLUMNS(vCalendar, "Weekday", WEEKDAY([Date]))

    VAR vTAT = 
        IF(vStartDate = vEndDate, 0,
            SWITCH(
                TRUE(),
                'WF'[WeekendType] = 2, COUNTX(FILTER(vWeekdays, AND([Weekday] <> 6, [Weekday] <> 7)), [Date]) - 1, // Fri, Sat
                'WF'[WeekendType] = 3, COUNTX(FILTER(vWeekdays, AND([Weekday] <> 5, [Weekday] <> 6)), [Date]) - 1, // Thu, Fri
                'WF'[WeekendType] = 4, COUNTX(FILTER(vWeekdays, AND([Weekday] <> 6, [Weekday] <> 1)), [Date]) - 1, // Fri, Sun
                ...
            )
        )

[danextian]

Hi @olimilo 

The holidays table must contain only one column and you dont really need to filter the holidays by the start and end dates. 

Networkdays Measure =
VAR _holidays =
    SELECTCOLUMNS ( FILTER ( Holidays, Holidays[Country] = "A" ), [Holidays] )
RETURN
    NETWORKDAYS (
        SELECTEDVALUE ( Start_End[End] ),
        SELECTEDVALUE ( Start_End[End] ),
        1,
        _holidays
    )

Networkdays Column =
VAR _holidays =
    SELECTCOLUMNS ( FILTER ( Holidays, Holidays[Country] = "A" ), [Holidays] )
RETURN
    NETWORKDAYS (
        Start_End[End],
        Start_End[End],
        1,
        _holidays
    )

1 means weekends are ommitted and are no included in counting. DAX NETWORKDAYS is inclusive of the start and end dates.

[olimilo]

Hi Dan, thanks for the response. This means then that it is not possible to indicate weekends outside of what is available in the KB? We are trying to avoid having to create a calculated calendar per entry in the table which is what we previously did to compute for the TATs.

[danextian]

What do you mean by to indicate weekends outside of what is available in the KB? I believe the options to indicate which days are the weekends is pretty comprehensive.

[olimilo]

You missed my other question:

Another question is, is there a way to add a custom weekend identifier? According to the KB, there is no weekend identifier for Fri+Sun

[danextian · Accepted solution]

You will need to do it the old school way - a dates table that indicates whether a date is a weekend or not, a calculation that counts/sums up the days within the range that are deemed to be weekdays only and exclude holidays.

NETWORKDAYS (old school) = 
VAR __COUNTRY = "A" -- can refer to a column
VAR __HOLIDAYS =
    SELECTCOLUMNS ( FILTER ( Holidays, Holidays[Country] = __COUNTRY ), [Holidays] )
VAR __RESULT =
    CALCULATE (
        SUM ( 'Calendar'[Weekend?] ),
        FILTER (
            VALUES ( 'Calendar'[Date] ),
            'Calendar'[Date] >= Start_End[Start]
                && 'Calendar'[Date] <= Start_End[End]
                && NOT 'Calendar'[Date] IN __HOLIDAYS
        )
    )
RETURN
    __RESULT

 Please see attached sample pbix.