OK, all of my recent work on equinoxes, solstices and Orthodox Easter were really geared toward publishing this quick measure for Ukrainian Holidays. My heart goes out to Ukrainians everywhere. Let us all pray for peace.
Ukrainian 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)
{
{"UA","New Year's Day", "Fixed", 1, 1, BLANK(), BLANK()},
{"UA","Orthodox Christmas Day", "Fixed", 1, 7, BLANK(), BLANK()},
{"UA","Orthodox New Year", "Fixed", 1, 14, BLANK(), BLANK()},
{"UA","Ukrainian Unity Day", "Fixed", 1, 22, BLANK(), BLANK()},
{"UA","Valentine's Day", "Fixed", 2, 14, BLANK(), BLANK()},
{"UA","Unity Day", "Fixed", 2, 16, BLANK(), BLANK()},
{"UA","International Women's Day", "Fixed", 3, 8, BLANK(), BLANK()},
{"UA","March Equinox", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"UA","April Fools", "Fixed", 4, 1, BLANK(), BLANK()},
{"UA","Orthodox Easter Day", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"UA","Orthodox Easter Monday", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"UA","Labor Day", "Fixed", 5, 1, BLANK(), BLANK()},
{"UA","Mother's Day", "Floating", 5, BLANK(), 1, 2},
{"UA","Victory Day", "Fixed", 5, 9, BLANK(), BLANK()},
{"UA","Europe Day", "Floating", 5, BLANK(), 7, 3},
{"UA","Kyiv Day", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"UA","Child Protection Day", "Fixed", 6, 1, BLANK(), BLANK()},
{"UA","Journalists' Day", "Fixed", 6, 6, BLANK(), BLANK()},
{"UA","Orthodox Pentecost", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"UA","Orthodox Pentecost Holiday", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"UA","June Solstice", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"UA","Day of Mourning and Commemoration of War Victims", "Fixed", 6, 22, BLANK(), BLANK()},
{"UA","Consitution Day", "Fixed", 6, 28, BLANK(), BLANK()},
{"UA","Navy Day", "Floating", 7, BLANK(), 1, 1},
{"UA","Kupala Night", "Fixed", 7, 6, BLANK(), BLANK()},
{"UA","Family Day", "Fixed", 7, 8, BLANK(), BLANK()},
{"UA","Baptism of Kyivan Rus", "Fixed", 7, 28, BLANK(), BLANK()},
{"UA","Ukrainian Statehood Day", "Fixed", 7, 28, BLANK(), BLANK()},
{"UA","Independence Day", "Fixed", 8, 24, BLANK(), BLANK()},
{"UA","September Equinox", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"UA","Day of Defenders and Defenders of Ukraine", "Fixed", 10, 14, BLANK(), BLANK()},
{"UA","Cultural Workers and Folk Artists Day", "Fixed", 11, 9, BLANK(), BLANK()},
{"UA","Dignity and Freedom Day", "Fixed", 11, 21, BLANK(), BLANK()},
{"UA","Army Day", "Fixed", 12, 6, BLANK(), BLANK()},
{"UA","St. Nicholas Day", "Fixed", 12, 19, BLANK(), BLANK()},
{"UA","December Solstice", "Calculated", BLANK(), BLANK(), BLANK(), BLANK()},
{"UA","Catholic Christmas Day", "Fixed", 12, 25, BLANK(), BLANK()}
}
)
VAR __OrthodoxEaster = [Orthodox Easter]
VAR __OrthodoxPentacost = __OrthodoxEaster + 49
VAR __KyivDay = DATE(__Year, 5, MAXX(FILTER(__Calendar, [Month]=5 && [Weekday] = 1),[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],
"March Equinox",TRUNC([March Equinox]),
"June Solstice",TRUNC([June Solstice]),
"September Equinox",TRUNC([September Equinox]),
"December Solstice",TRUNC([December Solstice]),
"Orthodox Easter Day",__OrthodoxEaster,
"Orthodox Easter Monday",__OrthodoxEaster + 1,
"Orthodox Pentecost",__OrthodoxPentacost,
"Orthodox Pentecost Holiday",__OrthodoxPentacost + 1,
"Kyiv Day",__KyivDay
)
)
)
VAR __HolidayNames = FILTER(__Holidays, [HolidayDate] = __Date)
VAR __CountOfHolidayNames = COUNTROWS(__HolidayNames)
RETURN
SWITCH(TRUE(),
ISBLANK(__CountOfHolidayNames),BLANK(),
CONCATENATEX(__HolidayNames, [Name], ", ")
)
Referrences:
- https://www.timeanddate.com/
- Jean Meeus' Astrological Algorithms
@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Learn Power BI 2nd Edition
