cancel
Showing results for
Did you mean:
Resolver I

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

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?

1 ACCEPTED SOLUTION
Super User

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).

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Super User

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).

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors