Forum Discussion
Help with Calculated Column for IsWorkday to Exclude Bank Holidays
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.
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.
Perfect - Thank you so much!
