Forum Discussion

olimilo's avatar
olimilo
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

NETWORKDAYS() Holiday question

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
                ...
            )
        )
  • 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.

5 Replies

  • 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's avatar
      olimilo
      Icon for Post Prodigy rankPost Prodigy

      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's avatar
        danextian
        Icon for Super User rankSuper User

        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.