Forum Discussion

lennox25's avatar
lennox25
Post Patron
2 years ago
Solved

Help with Calculated Column for IsWorkday to Exclude Bank Holidays

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

Need Help
  • amustafa's avatar
    amustafa
    2 years ago

    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.

2 Replies

  • amustafa's avatar
    amustafa
    Solution Sage
    2 years ago

    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.