Forum Discussion

olimilo's avatar
olimilo
Post 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...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

danextian's avatar
danextian
Super User
1 year ago

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
Post Prodigy
1 year ago

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
    Super User
    1 year ago

    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's avatar
      olimilo
      Post Prodigy
      1 year ago

      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's avatar
        danextian
        Super User
        1 year ago

        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.