Forum Discussion
NETWORKDAYS() Holiday question
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 __RESULTPlease see attached sample pbix.
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.
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.
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
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 __RESULTPlease see attached sample pbix.