Add working days and non-working days to date dimension table

[JemmaD]

Hi there

I have a date dimension table, and a separate table with bank holidays. I want to create a row in my date dimension table with 1 for a working day and 0 for a non-working day. So non-working days would be weekends and bank holidays. 

I guess I can do an If=Saturdate or If=Sunday for weekends, but how do I incorporate my bank holidays into the date table?

[DataInsights · Accepted solution]

@JemmaD,

Try this calculated column in your date dimension table. There is no relationship between DimDate and BankHolidays.

Is Bank Holiday =
IF (
    NOT ISEMPTY ( FILTER ( BankHolidays, BankHolidays[Date] = DimDate[Date] ) ),
    1
)

You can incorporate this column in your Working Day column (if the date is a weekend or bank holiday, then 0, else 1).