Forum Discussion
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 __RESULTPlease see attached sample pbix.
5 Replies
- danextian
Super User
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
Post 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
Super 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.