Dynamic Holiday Calendar

[deaconb]

Sharing this solution in case anyone else finds it useful.

My company closes on US Federal Holidays and some additonal dates. The difficulty with identifying this data year after year is that some of these dates are dyanmic based on the week in a month or when they are observed if falling on a weekend. Obviously the static dates are easy year after year. 

After a lot of piecing together and trial/error, here's the measure I created w/notes on what the date represents:

isholiday =

SWITCH(([Date]), 
DATE(YEAR([Date]), 01, 01), "Y", // New Year's Day 
IF(WEEKDAY(DATE(YEAR([Date]), 01, 01), 2) = 7, DATE(YEAR([Date]), 01, 02),""), "Y", // New Year's Day on Sunday, observed 02Jan
IF(MONTH([Date]) = 1, CEILING(EOMONTH([Date],-1)-1,7)+16, ""), "Y", //MLK Day (3rd Monday in Jan)
IF(MONTH([Date]) = 2, CEILING(EOMONTH([Date],-1)-1,7)+16, ""), "Y", //President's Day (3rd Monday in Feb)
IF(MONTH([Date]) = 5, CEILING(EOMONTH([Date], 0)-1,7)-5, ""), "Y",//Memorial Day
DATE(YEAR([Date]), 06, 19), "Y", //Juneteenth 
IF(WEEKDAY(DATE(YEAR([Date]), 06, 19), 2) = 6, DATE(YEAR([Date]), 06, 18),""), "Y", // Juneteenth on Saturday, observed 18Jun
IF(WEEKDAY(DATE(YEAR([Date]), 06, 19), 2) = 7, DATE(YEAR([Date]), 06, 20),""), "Y", // Juneteenth on Sunday, observed 20Jun
DATE(YEAR([Date]), 07, 04), "Y", // July 4th 
IF(WEEKDAY(DATE(YEAR([Date]), 07, 04), 2) = 6, DATE(YEAR([Date]), 07, 03),""), "Y", // July 4th on Saturday, observed 03Jul
IF(WEEKDAY(DATE(YEAR([Date]), 07, 04), 2) = 7, DATE(YEAR([Date]), 07, 05),""), "Y", // July 4th on Sunday, observed 05Jul
IF(MONTH([Date]) = 9, CEILING(EOMONTH([Date],-1)-1,7)+2, ""), "Y", //Labor Day (1st Monday in Sep)
IF(MONTH([Date]) = 11, CEILING(EOMONTH([Date],-1)-4,7)+26, ""), "Y", //Thanksgiving (4th Thursday in Nov)
IF(MONTH([Date]) = 11, CEILING(EOMONTH(DATETABLE[Date],-1)-4,7)+27, ""), "Y", //Day after Thanksgiving (4th Friday in Nov)
DATE(YEAR([Date]), 12, 24), "Y", //Chrismas Eve
DATE(YEAR([Date]), 12, 25), "Y", //Christmas Day
DATE(YEAR([Date]), 12, 26), "Y", //Closed
DATE(YEAR([Date]), 12, 27), "Y", //Closed
DATE(YEAR([Date]), 12, 28), "Y", //Closed
DATE(YEAR([Date]), 12, 29), "Y", //Closed
DATE(YEAR([Date]), 12, 30), "Y", //Closed
DATE(YEAR([Date]), 12, 31), "Y", //New Year's Eve
"")

Pre-requiste: Date table with a Date field. 

To assess if your date in another table is a holiday in your Date table (1:M or 1:1 relationship with additional tables), you can use this formula in a measure: 

IF(

CALCULATE(

MIN('DATETABLE'[isholiday]),

FILTER('DATETABLE', [Date] = MIN([yourdatetocompare]) && [isholiday] = "Y")) = "Y",

dothisiftrue, 

dothisiffalse)

Hope someone else finds this useful and if you do - cheers!

[deaconb · Accepted solution]

Don't need anyone to provide a solution. Apparently I didn't know how to just make a post and not accept replies/solutions.