Help with Calculated Column for IsWorkday to Exclude Bank Holidays

[lennox25]

I have this column 'IsWorkDay' but it wont pick up the EnglandWales Holiday its counting all as 0?

IsWorkDay = IF(OR(DateTable'[WeekDayNo] = 1, DateTable[WeekDayNo]=7),0,IF(ISBLANK(Datetable[EnglandWalesHoliday]),1,0)) 

Can anyone help to tell me what I've done wrong? Thank you

[amustafa · Accepted solution]

Based on the sample data you've provided, it appears that the `EnglandWalesHoliday` column is using `1` to indicate a holiday and `0` for non-holidays. This differs from the initial assumption that the column would be blank for non-holidays.

Given this, you need to adjust your DAX formula to properly check for a `1` instead of using `ISBLANK`. Here's the revised DAX formula:

IsWorkDay = IF(
OR(DateTable[WeekDayNo] = 1, DateTable[WeekDayNo] = 7),
0,
IF(DateTable[EnglandWalesHoliday] = 1, 0, 1)
)

This formula will return `0` if the day is either a weekend (WeekDayNo = 1 or 7) or a holiday (EnglandWalesHoliday = 1), and `1` otherwise.

Please replace `DateTable` with the actual name of your table if it is different. After updating the formula in your environment, it should correctly reflect whether a day is a workday or not.

[lennox25]

Perfect - Thank you so much!