Forum Discussion
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...
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.
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
__RESULT
Please see attached sample pbix.