Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
eyJrIjoiMTc3Nzg5ZjYtNzJmNS00YzRjLThjNmMtMGVhNmJkMDY4NGJhIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9
Amazing solidarity! Thank you, Greg!