How to pass a list of holidays to the NETWORKDAYS function

[mp390988]

Hi,

I have a list of holidays in my Calendar table as per below:

I am trying to work out the DailyAvg as follows:

DailyAvg = 
VAR TotalRevenue = CALCULATE(SUM(DealerTrgts[Revenue]))


VAR NumDays = NETWORKDAYS(
    DATE(YEAR(TODAY()), MONTH(TODAY()),1), 
    TODAY(),
    1,
    FILTER(
        ALL('Calendar'[Date]),
        'Calendar'[Date]=TODAY() && 'Calendar'[IsHoliday] = 1
    )
)
RETURN IF(
    NumDays > 0, 
    round(TotalRevenue / NumDays,2), 
    0
)

But it is complaining saying a single value for column 'IsHoliday' in table 'Calendar' cannot be determined. This can happen when a measure or function formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or um to get a single result.

Anyone know how to pass a list of holidays to the NETWORKDAYS function?

Thanks

[danextian]

Hi @mp390988 

Holidays should be a single column only but you're retunng a filtered table with multipole columns. Also, if you need the first day of the month, it's been re-written as below.

DailyAvg =
VAR TotalRevenue =
    CALCULATE ( SUM ( DealerTrgts[Revenue] ) )
VAR StartDate =
    EOMONTH ( TODAY (), -1 ) + 1
VAR EndDate =
    TODAY ()
VAR Holidays =
    CALCULATETABLE ( VALUES ( 'Calendar'[Date] ), 'Calendar'[IsHoliday] = 1 )
VAR NumDays =
    NETWORKDAYS ( StartDate, EndDate, 1, Holidays )
RETURN
    IF ( NumDays > 0, ROUND ( TotalRevenue / NumDays, 2 ), 0 )

[rohit1991 · Accepted solution]

Hi @mp390988 ,

The NETWORKDAYS function in DAX needs a single-column table of holiday dates not a filter, not a boolean, just the dates. If your Calendar table has [Date] and [IsHoliday], you want to grab only the holiday dates and pass those in. Here’s what works for me:
DailyAvg =
VAR TotalRevenue = CALCULATE(SUM(DealerTargets[Revenue]))
VAR NumDays =NETWORKDAYS( DATE(YEAR(TODAY()), MONTH(TODAY()), 1),TODAY(),
SELECTCOLUMNS(
FILTER('Calendar', 'Calendar'[IsHoliday] = 1),
"HolidayDate", 'Calendar'[Date]
))RETURN
IF(NumDays > 0,ROUND(TotalRevenue / NumDays, 2),0)

Why this works:

• SELECTCOLUMNS(..., "HolidayDate", 'Calendar'[Date]) makes sure you’re passing exactly what NETWORKDAYS wants: just the date column for your holidays.
  
  

If your [IsHoliday] column uses something other than 1/0 (like TRUE/FALSE or Yes/No), just tweak the filter bit to match. If you have a separate Holidays table, it’s even easier just pass the [Date] column from that table as your holidays argument.