cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Inconsistent holiday

Background: 

The company took the following holidays for 2018 - 

  • New Year’s Day (Jan 1)
  • Presidents’ Day (Feb 19)
  • Memorial Day (May 28)
  • Independence Day (July 4)
  • Labor Day (Sept 3)
  • Thanksgiving Day & day after (Nov 22 & 23)
  • Christmas Eve & Christmas Day (Dec 24 & 25)
  • New Year’s Eve (Dec 31)

However, in previous years, the Presidents' Day was NOT a holiday and MLK Day WAS a holiday.

 

I have a date table which contains all dates and corresponding holiday names with dates.I also have all the weekdays with 0=Sunday, 1=Monday, etc.  I would like to create a new column that is True False for working day vs. nonworking day. How do I create a column that reflects all years? 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Inconsistent holiday

Create a new table called holiday to store the holiday information such as below.

2018-04-26_9-35-00.png

Create relationship between the holiday table with your original calendar table.

 

 

2018-04-26_9-38-07.png

 

Then use the following Dax to create a calcuated column to mark the working days

 

 

Isworkingday = IF('Calendar'[Weekday]>0&&'Calendar'[Weekday]<6&&COUNTX(RELATEDTABLE(Holiday),1)<1,"Yes","No")

2018-04-26_9-40-48.png

 

 

 

 

View solution in original post

1 REPLY 1
Highlighted
Solution Sage
Solution Sage

Re: Inconsistent holiday

Create a new table called holiday to store the holiday information such as below.

2018-04-26_9-35-00.png

Create relationship between the holiday table with your original calendar table.

 

 

2018-04-26_9-38-07.png

 

Then use the following Dax to create a calcuated column to mark the working days

 

 

Isworkingday = IF('Calendar'[Weekday]>0&&'Calendar'[Weekday]<6&&COUNTX(RELATEDTABLE(Holiday),1)<1,"Yes","No")

2018-04-26_9-40-48.png

 

 

 

 

View solution in original post

Helpful resources

Announcements
Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors