OK, inspired by Happy Easter, I spent some time here: Holidays and observances in United States in 2022 (timeanddate.com) and came up with this quick measure to identify various different holidays, events and observances in the US. No, it's not utterly comprehensive but if you find something missing it is easily modified to include fixed events (on a specific day of a specific month of a year), floating events (the second Tuesday of March) and the oddly calculated events, like Easter or Election Day. Shoud be adaptable to other countries holidays as well. Enjoy! 🙂
US Holidays =
VAR __Date = MAX('Dates'[Date])
VAR __Year = YEAR(__Date)
VAR __Month = MONTH(__Date)
VAR __Day = DAY(__Date)
VAR __Calender1 =
ADDCOLUMNS(
CALENDAR(DATE(__Year, 1, 1), DATE(__Year, 12, 31)),
"Month",MONTH([Date]),
"Day",DAY([Date]),
"Weekday",WEEKDAY([Date],1),
"Weeknum",WEEKNUM([Date],1)
)
VAR __Calendar =
ADDCOLUMNS(
__Calender1,
"WeekdayNum",
COUNTROWS(
FILTER(__Calender1,
[Month] = EARLIER([Month]) &&
[Weekday] = EARLIER([Weekday]) &&
[Day] <= EARLIER([Day])
)
)
)
VAR __Holidays1 =
DATATABLE(
"Country", STRING, // For now, US
"Name", STRING, // Name of holiday
"Type", STRING, // Fixed, Floating or Calculated
"HolidayMonth", INTEGER, // Month number (1-12)
"HolidayDay", INTEGER, // Day of holiday if Fixed
"HolidayWeekday", INTEGER, // Weekday (1 = Sunday, 7 = Saturday)
"HolidayWeeknum", INTEGER, // 1 - 4 occurrence of weekday (first Sunday in April would be 1)
{
{"US","New Year's Day", "Fixed", 1, 1, BLANK(), BLANK()},
{"US","Epiphany", "Fixed", 1, 6, BLANK(), BLANK()},
{"US","Groundhog Day", "Fixed", 2, 2, BLANK(), BLANK()},
{"US","National Wear Red Day", "Floating", 2, BLANK(), 6, 1},
{"US","Super Bowl", "Floating", 2, BLANK(), 1, 2},
{"US","Martin Luther King Day", "Floating", 1, BLANK(), 2, 3},
{"US","Presidents' Day", "Floating", 2, BLANK(), 2, 3},
{"US","Valentine's Day", "Fixed", 2, 14, BLANK(), BLANK()},
{"US","Pi Day", "Fixed", 3, 14, BLANK(), BLANK()},
{"US","St. Patrick's Day", "Fixed", 3, 17, BLANK(), BLANK()},
{"US","April Fools", "Fixed", 4, 1, BLANK(), BLANK()},
{"US","Tax Day", "Fixed", 4, 15, BLANK(), BLANK()},
{"US","Take Your Kid to Work Day", "Floating", 4, BLANK(), 5, 4},
{"US","May the Fourth Be With You Day", "Fixed", 5, 4, BLANK(), BLANK()},
{"US","Cinco de Mayo", "Fixed", 5, 5, BLANK(), BLANK()},
{"US","Kentucky Oaks", "Floating", 5, BLANK(), 6, 1},
{"US","Kentucky Derby", "Floating", 5, BLANK(), 7, 1},
{"US","Victory in Europe Day", "Fixed", 5, 8, BLANK(), BLANK()},
{"US","Peace Officers Memorial Day", "Fixed", 5, 15, BLANK(), BLANK()},
{"US","National Day of Prayer", "Floating", 5, BLANK(), 5, 1},
{"US","Mother's Day", "Floating", 5, BLANK(), 1, 2},
{"US","Armed Forces Day", "Floating", 5, BLANK(), 7, 3},
{"US","National Maritime Day", "Fixed", 5, 22, BLANK(), BLANK()},
{"US","Missing Children's Day", "Fixed", 5, 25, BLANK(), BLANK()},
{"US","Memorial Day", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"US","Father's Day", "Floating", 6, BLANK(), 1, 3},
{"US","D-Day", "Fixed", 6, 6, BLANK(), BLANK()},
{"US","Flag Day", "Fixed", 6, 14, BLANK(), BLANK()},
{"US","Juneteenth", "Fixed", 6, 19, BLANK(), BLANK()},
{"US","Independence Day", "Fixed", 7, 4, BLANK(), BLANK()},
{"US","Bastille Day", "Fixed", 7, 14, BLANK(), BLANK()},
{"US","Labor Day", "Floating", 9, BLANK(), 2, 1},
{"US","Patriot Day", "Fixed", 9, 11, BLANK(), BLANK()},
{"US","Halloween", "Fixed", 10, 31, BLANK(), BLANK()},
{"US","All Saint's Day", "Fixed", 11, 1, BLANK(), BLANK()},
{"US","All Souls's Day", "Fixed", 11, 2, BLANK(), BLANK()},
{"US","Thanksgiving", "Floating", 11, BLANK(), 5, 4},
{"US","Black Friday", "Floating", 11, BLANK(), 6, 4},
{"US","St. Andrews Day", "Fixed", 11, 30, BLANK(), BLANK()},
{"US","St. Nicholas Day", "Fixed", 12, 6, BLANK(), BLANK()},
{"US","Pearl Harbor Day", "Fixed", 12, 7, BLANK(), BLANK()},
{"US","Bill of Rights Day", "Fixed", 12, 15, BLANK(), BLANK()},
{"US","Wright Brothers Day", "Fixed", 12, 15, BLANK(), BLANK()},
{"US","Christmas Eve", "Fixed", 12, 24, BLANK(), BLANK()},
{"US","Christmas", "Fixed", 12, 25, BLANK(), BLANK()},
{"US","Kwanzaa", "Fixed", 12, 26, BLANK(), BLANK()},
{"US","New Year's Eve", "Fixed", 12, 31, BLANK(), BLANK()},
{"US","Easter", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"US","Ash Wednesday", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"US","Election Day", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"US","First Sunday of Advent", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"US","Pentecost", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"US","Trinity Sunday", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"US","Preakness Stakes", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"US","Belmont Stakes", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()}
}
)
VAR __Easter =
// Original code: https://www.assa.org.au/edm
// For years 1583 to 4099
VAR __FirstDigit = TRUNC(__Year/100)
VAR __Remainder19 = MOD(__Year, 19)
VAR __PFMDate1 = TRUNC((__FirstDigit - 15) / 2) + 202 - 11 * __Remainder19
VAR __PFMDate2 =
SWITCH(TRUE(),
__FirstDigit IN {21, 24, 25, 27, 28, 29, 30, 31, 32, 34, 35, 38}, __PFMDate1 - 1,
__FirstDigit IN {33, 36, 37, 39, 40}, __PFMDate1 - 2,
__PFMDate1
)
VAR __PFMDate = MOD(__PFMDate2, 30)
VAR __tA =
SWITCH(TRUE(),
__PFMDate = 29 || (__PFMDate = 29 && __Remainder19 > 10), __PFMDate + 20,
__PFMDate + 21
)
VAR __tB = MOD(__tA - 19, 7)
VAR __tC1 = MOD(40 - __FirstDigit, 4)
VAR __tC2 = IF(__tC1 = 3, __tC1 + 1, __tC1)
VAR __tC = IF(__tC2 > 1, __tC2 + 1, __tC2)
VAR __tD1 = MOD(__Year,100)
VAR __tD = MOD( __tD1 + TRUNC(__tD1/4), 7)
VAR __tE = MOD(20 - __tB - __tC - __tD, 7) + 1
VAR __d = __tA + __tE
VAR __EasterDay = IF(__d > 31, __d - 31, __d)
VAR __EasterMonth = IF(__d > 31, 4, 3)
VAR __EasterYear = __Year
RETURN
DATE(__EasterYear, __EasterMonth, __EasterDay)
VAR __AshWednesday = __Easter - 46
VAR __Pentecost = __Easter + 49
VAR __Trinity = __Easter + 56
VAR __StAndrewsDay = DATE(__Year,11,30)
VAR __Advent =
VAR __StAndrewsWeekday = WEEKDAY(__StAndrewsDay)
RETURN
SWITCH(TRUE(),
__StAndrewsWeekday = 1,__StAndrewsDay,
__StAndrewsWeekday < 5, __StAndrewsDay - __StAndrewsWeekday + 1,
__StAndrewsDay + 8 - __StAndrewsDay
)
VAR __ElectionDay = DATE(__Year,11, MINX(FILTER(__Calendar,[Month] = 11 && [Weekday] = 2),[Day]) + 1)
VAR __Derby = DATE(__Year, 5, MINX(FILTER(__Calendar, [Month]=5 && [WeekdayNum] = 1 && [Weekday] = 7),[Day]))
VAR __Preakness = __Derby + 14
VAR __Belmont = __Derby + 35
VAR __MemorialDay = DATE(__Year, 5, MAXX(FILTER(__Calendar, [Month]=5 && [Weekday] = 2),[Day]))
VAR __Holidays =
ADDCOLUMNS(
__Holidays1,
"HolidayDate",
SWITCH(TRUE(),
[Type] = "Fixed", DATE(__Year,[HolidayMonth],[HolidayDay]),
[Type] = "Floating",
MAXX(
FILTER(__Calendar,
[Month] = [HolidayMonth] &&
[WeekdayNum] = [HolidayWeeknum] &&
[Weekday] = [HolidayWeekday]
),
[Date]
),
SWITCH([Name],
"Easter",__Easter,
"Ash Wednesday",__AshWednesday,
"Election Day",__ElectionDay,
"First Sunday of Advent",__Advent,
"Pentecost",__Pentecost,
"Trinity Sunday", __Trinity,
"Preakness Stakes", __Preakness,
"Belmont Stakes", __Belmont,
"Memorial Day", __MemorialDay
)
)
)
VAR __HolidayNames = FILTER(__Holidays, [HolidayDate] = __Date)
VAR __CountOfHolidayNames = COUNTROWS(__HolidayNames)
RETURN
SWITCH(TRUE(),
ISBLANK(__CountOfHolidayNames),BLANK(),
CONCATENATEX(__HolidayNames, [Name], ", ")
)
To use with MSHGQM or Quick Measures Pro (Enterprise DNA) simply modify VAR __Date to below:
VAR __Date = __AGGREGATION__(__COLUMN__)
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Mastering Power BI 2nd Edition
DAX is easy, CALCULATE makes DAX hard...
