DAX Table Code 1.
Date =
------------------------------------------------------------
--
-- Configuration
--
------------------------------------------------------------
VAR TodayReference =
TODAY () -- Change this if you need to use another date as a reference "current" day
VAR FirstYear =
YEAR ( TodayReference )-7
VAR LastYear =
YEAR ( TodayReference )+5
VAR FiscalCalendarFirstMonth = 1 -- For Fiscal 52-53 weeks (start depends on rules) and Gregorian (starts on the first of the month)
VAR FirstDayOfWeek = 0 -- Use: 0 - Sunday, 1 - Monday, 2 - Tuesday, ... 5 - Friday, 6 - Saturday
VAR IsoCountryHolidays = "US" -- Use only supported ISO countries or "" for no holidays
VAR WeeklyType = "Last" -- Use: "Nearest" or "Last"
VAR QuarterWeekType = "445" -- Supports only "445", "454", and "544"
VAR CalendarRange = "Calendar" -- Supports "Calendar", "FiscalGregorian", "FiscalWeekly"
-- Last: for last weekday of the month at fiscal year end
-- Nearest: for last weekday nearest the end of month
-- Reference for Last/Nearest definition: https://en.wikipedia.org/wiki/4%E2%80%934%E2%80%935_calendar)
--
-- For ISO calendar use
-- FiscalCalendarFirstMonth = 1 (ISO always starts in January)
-- FirstDayOfWeek = 1 (ISO always starts on Monday)
-- WeeklyType = "Nearest" (ISO use the nearest week type algorithm)
-- For US with last Saturday of the month at fiscal year end
-- FirstDayOfWeek = 0 (US weeks start on Sunday)
-- WeeklyType = "Last"
-- For US with last Saturday nearest the end of month
-- FirstDayOfWeek = 0 (US weeks start on Sunday)
-- WeeklyType = "Nearest"
--
------------------------------
VAR CalendarGregorianPrefix = "" -- Prefix used in columns of standard Gregorian calendar
VAR FiscalGregorianPrefix = "F" -- Prefix used in columns of fiscal Gregorian calendar
VAR FiscalWeeklyPrefix = "FW " -- Prefix used in columns of fiscal weekly calendar
VAR WorkingDayType = "Working day" -- Description for working days
VAR NonWorkingDayType = "Non-working day" -- Description for non-working days
------------------------------
VAR WeeklyCalendarType = "Weekly" -- Supports "Weekly", "Custom"
-- Set the working days - 0 = Sunday, 1 = Monday, ... 7 = Saturday
VAR WorkingDays =
DATATABLE ( "WorkingDayNumber", INTEGER, { { 1 }, { 2 }, { 3 }, { 4 }, { 5 } } ) --
-- Use CustomFiscalPeriods in case you need arbitrary definition of weekly fiscal years
-- The first day of each year must be a weekday corresponding to the definition of FirstDayOfWeek
VAR CustomFiscalPeriods =
DATATABLE (
"Fiscal YearNumber", INTEGER,
"FirstDayOfYear", DATETIME,
"LastDayOfYear", DATETIME,
{
{ 2016, "2015-06-28", "2016-07-02" },
{ 2017, "2016-07-03", "2017-07-01" },
{ 2018, "2017-07-02", "2018-06-30" },
{ 2019, "2018-07-01", "2019-06-29" }
}
) ------------------------------------------------------------
--
-- End of General Configuration
--
------------------------------------------------------------
--
-- The following variables define specific parameters
-- for calendars - you should modify them only to
-- change configuration of specific countries, translate
-- names of holidays, or to add configuration for other
-- countries
--
------------------------------------------------------------
VAR InLieuOf_prefix = "(in lieu of " -- prefix of substitute holidays
VAR InLieuOf_suffix = ")" -- prefix of substitute holidays
VAR HolidayParameters =
DATATABLE (
"ISO Country", STRING,
-- ISO country code (to enable filter based on country)
"MonthNumber", INTEGER,
-- Number of month
"DayNumber", INTEGER,
-- Absolute day (ignore WeekDayNumber, otherwise use 0)
"WeekDayNumber", INTEGER,
-- 0 = Sunday, 1 = Monday, ... , 7 = Saturday
"OffsetWeek", INTEGER,
-- 1 = first, 2 = second, ... -1 = last, -2 = second-last, ...
"HolidayName", STRING,
-- Holiday name
"SubstituteHoliday", INTEGER,
-- 0 = no substituteHoliday, 1 = substitute holiday with next working day, 2 = substitute holiday with next working day
-- (use 2 before 1 only, e.g. Christmas = 2, Boxing Day = 1)
"ConflictPriority", INTEGER,
-- Priority in case of two or more holidays in the same date - lower number --> higher priority
-- For example: marking Easter relative days with 150 and other holidays with 100 means that other holidays take
-- precedence over Easter-related days; use 50 for Easter related holidays to invert such a priority
{
--
-- US = United States
{ "US", 1, 0, 1, 1, "New Year's Day", 0, 100 },
-- { "US", 1, 0, 1, 3, "Martin Luther King, Jr.", 0, 100 },
-- { "US", 2, 0, 1, 3, "Presidents' Day", 0, 100 },
// aka Washington's Birthday
{ "US", 5, 0, 1, -1, "Memorial Day", 0, 100 },
{ "US", 7, 0, 1, 1, "Independence Day", 0, 100 },
{ "US", 9, 0, 1, 1, "Labor Day", 0, 100 },
-- { "US", 10, 0, 1, 2, "Columbus Day", 0, 100 },
-- { "US", 11, 11, 0, 0, "Veterans Day", 0, 100 },
{ "US", 11, 0, 4, 4, "Thanksgiving Day", 0, 100 },
{ "US", 11, 0, 5, 4, "Thanksgiving Day Friday", 0, 100 },
{ "US", 12, 0, 3, 4, "Christmas Eve", 0, 100 },
{ "US", 12, 0, 4, 4, "Christmas Day", 0, 100 },
{ "US", 12, 0, 5, 4, "New Year's Eve", 0, 100 },
--
-- CA = Canada (include only nationwide and Thanksgiving)
{ "CA", 1, 1, 0, 0, "New Year's Day", 0, 100 },
{ "CA", 99, -2, 0, 0, "Good Friday", 0, 50 },
{ "CA", 7, 1, 0, 0, "Canada Day", 0, 100 },
{ "CA", 9, 0, 1, 1, "Labour Day", 0, 100 },
{ "CA", 10, 0, 1, 2, "Thanksgiving", 0, 100 },
{ "CA", 12, 25, 0, 0, "Christmas Day", 0, 100 },
--
-- UK = England (different configuration in Scotland and Northern Ireland)
{ "UK", 1, 1, 0, 0, "New Year's Day", 1, 100 },
{ "UK", 99, -2, 0, 0, "Good Friday", 0, 50 },
{ "UK", 99, 1, 0, 0, "Easter Monday", 0, 50 },
{ "UK", 5, 0, 1, 1, "May Day Bank Holiday", 0, 100 },
{ "UK", 5, 0, 1, -1, "Spring Bank Holiday", 0, 100 },
{ "UK", 8, 0, 1, -1, "Late Summer Bank Holiday", 0, 100 },
{ "UK", 12, 25, 0, 0, "Christmas Day", 2, 100 },
{ "UK", 12, 26, 0, 0, "Boxing Day", 1, 100 },
--
-- AU = Australia
{ "AU", 1, 1, 0, 0, "New Year's Day", 1, 100 },
{ "AU", 1, 26, 0, 0, "Australia Day", 1, 100},
{ "AU", 99, -2, 0, 0, "Good Friday", 0, 50 },
{ "AU", 99, 1, 0, 0, "Easter Monday", 0, 50 },
{ "AU", 4, 25, 0, 0, "Anzac Day", 1, 100 },
{ "AU", 12, 25, 0, 0, "Christmas Day", 2, 100 },
{ "AU", 12, 26, 0, 0, "Boxing Day", 1, 100 },
--
-- DE = Germany
{ "DE", 1, 1, 0, 0, "New Year's Day", 0, 100 },
{ "DE", 99, -2, 0, 0, "Good Friday", 0, 50 },
{ "DE", 99, 1, 0, 0, "Easter Monday", 0, 50 },
{ "DE", 5, 1, 0, 0, "Labour Day", 0, 100 },
{ "DE", 99, 39, 0, 0, "Ascension Day", 0, 50 },
{ "DE", 99, 50, 0, 0, "Whit Monday", 0, 50 },
{ "DE", 10, 3, 0, 0, "German Unity Day", 0, 100 },
{ "DE", 12, 25, 0, 0, "Christmas Day", 0, 100 },
{ "DE", 12, 26, 0, 0, "St. Stephen's Day", 0, 100 },
--
-- FR = France
{ "FR", 1, 1, 0, 0, "New Year's Day", 0, 100 },
{ "FR", 99, 1, 0, 0, "Easter Monday", 0, 50 },
{ "FR", 5, 1, 0, 0, "Labour Day", 0, 100 },
{ "FR", 5, 8, 0, 0, "Victor in Europe Day", 0, 100 },
{ "FR", 99, 39, 0, 0, "Ascension Day", 0, 50 },
{ "FR", 99, 50, 0, 0, "Whit Monday", 0, 50 },
{ "FR", 7, 14, 0, 0, "Bastille Day", 0, 100 },
{ "FR", 8, 15, 0, 0, "Assumption Day", 0, 100 },
{ "FR", 11, 1, 0, 0, "All Saints' Day", 0, 100 },
{ "FR", 11, 11, 0, 0, "Armistice Day", 0, 100 },
{ "FR", 12, 25, 0, 0, "Christmas Day", 0, 100 },
--
-- IT = Italy
{ "IT", 1, 1, 0, 0, "New Year's Day", 0, 100 },
{ "IT", 1, 6, 0, 0, "Epiphany", 0, 100 },
{ "IT", 99, 1, 0, 0, "Easter Monday", 0, 100 },
{ "IT", 4, 25, 0, 0, "Liberation Day", 0, 100 },
{ "IT", 5, 1, 0, 0, "Labour Day", 0, 100 },
{ "IT", 6, 2, 0, 0, "Republic Day", 0, 100 },
{ "IT", 8, 15, 0, 0, "Assumption Day", 0, 100 },
{ "IT", 11, 1, 0, 0, "All Saints' Day", 0, 100 },
{ "IT", 12, 8, 0, 0, "Immaculate Conception", 0, 100 },
{ "IT", 12, 25, 0, 0, "Christmas Day", 0, 100 },
{ "IT", 12, 26, 0, 0, "St. Stephen's Day", 0, 100 },
--
-- ES = Spain
{ "ES", 1, 1, 0, 0, "New Year's Day", 0, 100 },
{ "ES", 1, 6, 0, 0, "Epiphany", 0, 100 },
{ "ES", 99, -3, 0, 0, "Maundy Thursday", 0, 50 },
// Except Catalonia
{ "ES", 99, -2, 0, 0, "Good Friday", 0, 50 },
{ "ES", 99, 1, 0, 0, "Easter Monday", 0, 50 },
// Belearic Islands, Basque Country, Catalonia, La Rioja, Navarra and Valenciana only
{ "ES", 5, 1, 0, 0, "Labour Day", 0, 100 },
{ "ES", 8, 15, 0, 0, "Assumption Day", 0, 100 },
{ "ES", 10, 12, 0, 0, "Fiesta Navional de España", 0, 100 },
{ "ES", 11, 1, 0, 0, "All Saints' Day", 0, 100 },
{ "ES", 12, 6, 0, 0, "Constitution Day", 0, 100 },
{ "ES", 12, 8, 0, 0, "Immaculate Conception", 0, 100 },
{ "ES", 12, 25, 0, 0, "Christmas Day", 0, 100 },
--
-- NL = The Netherlands
{ "NL", 1, 1, 0, 0, "New Year's Day", 0, 100 },
{ "NL", 99, 1, 0, 0, "Easter Monday", 0, 50 },
{ "NL", 99, 39, 0, 0, "Ascension Day", 0, 50 },
{ "NL", 99, 50, 0, 0, "Whit Monday", 0, 50 },
{ "NL", 4, 27, 0, 0, "King's Day", 0, 100 },
// King's day shifted to Saturday if on a Sunday - not handled in this calendar
{ "NL", 5, 5, 0, 0, "Liberation Day", 0, 100 },
{ "NL", 12, 25, 0, 0, "Christmas Day", 0, 100 },
{ "NL", 12, 26, 0, 0, "St. Stephen's Day", 0, 100 },
--
-- BE = Belgium
{ "BE", 1, 1, 0, 0, "New Year's Day", 0, 100 },
{ "BE", 99, 1, 0, 0, "Easter Monday", 0, 50 },
{ "BE", 99, 39, 0, 0, "Ascension Day", 0, 50 },
{ "BE", 99, 50, 0, 0, "Whit Monday", 0, 50 },
{ "BE", 5, 1, 0, 0, "Labour Day", 0, 100 },
{ "BE", 7, 21, 0, 0, "Belgian National DayDay", 0, 100 },
{ "BE", 8, 15, 0, 0, "Assumption Day", 0, 100 },
{ "BE", 11, 1, 0, 0, "All Saints' Day", 0, 100 },
{ "BE", 11, 11, 0, 0, "Armistice Day", 0, 100 },
{ "BE", 12, 25, 0, 0, "Christmas Day", 0, 100 },
--
-- PT = Portugal
{ "PT", 1, 1, 0, 0, "New Year's Day", 0, 100 },
{ "PT", 99, -2, 0, 0, "Good Friday", 0, 50 },
{ "PT", 99, 60, 0, 0, "Corpus Christi", 0, 50 },
{ "PT", 4, 25, 0, 0, "Freedom Day", 0, 100 },
{ "PT", 5, 1, 0, 0, "Labour Day", 0, 100 },
{ "PT", 6, 10, 0, 0, "Portugal Day", 0, 100 },
{ "PT", 8, 15, 0, 0, "Assumption Day", 0, 100 },
{ "PT", 10, 5, 0, 0, "Republic Day", 0, 100 },
{ "PT", 11, 1, 0, 0, "All Saints' Day", 0, 100 },
{ "PT", 12, 1, 0, 0, "Restoration of Independence", 0, 100 },
{ "PT", 12, 8, 0, 0, "Immaculate Conception", 0, 100 },
{ "PT", 12, 25, 0, 0, "Christmas Day", 0, 100 }
}
)
VAR HolidayDates_ConfigGeneration =
FILTER (
HolidayParameters,
IF (
CONTAINS ( HolidayParameters, [ISO Country], IsoCountryHolidays )
|| IsoCountryHolidays = "",
[ISO Country] = IsoCountryHolidays,
ERROR ( "IsoCountryHolidays set to an unsupported contry code" )
)
)
VAR HolidayDates_GeneratedRawWithDuplicates =
GENERATE (
GENERATE (
GENERATESERIES ( FirstYear - 1, LastYear + 1, 1 ),
HolidayDates_ConfigGeneration
),
VAR HolidayYear = [Value]
VAR EasterDate =
-- Code adapted from original VB version from https://www.assa.org.au/edm
VAR EasterYear = HolidayYear
VAR FirstDig =
INT ( EasterYear / 100 )
VAR Remain19 =
MOD ( EasterYear, 19 ) //
-- Calculate PFM date
VAR temp1 =
MOD (
INT ( ( FirstDig - 15 ) / 2 )
+ 202
- 11 * Remain19
+ SWITCH (
TRUE,
FirstDig IN { 21, 24, 25, 27, 28, 29, 30, 31, 32, 34, 35, 38 }, -1,
FirstDig IN { 33, 36, 37, 39, 40 }, -2,
0
),
30
)
VAR tA =
temp1 + 21
+ IF ( temp1 = 29 || ( temp1 = 28 && Remain19 > 10 ), -1 ) //
-- Find the next Sunday
VAR tB =
MOD ( tA - 19, 7 )
VAR tCpre =
MOD ( 40 - FirstDig, 4 )
VAR tC =
tCpre
+ IF ( tCpre = 3, 1 )
+ IF ( tCpre > 1, 1 )
VAR temp2 =
MOD ( EasterYear, 100 )
VAR tD =
MOD ( temp2 + INT ( temp2 / 4 ), 7 )
VAR tE =
MOD ( 20 - tB - tC - tD, 7 )
+ 1
VAR d = tA + tE //
-- Return the date
VAR EasterDay =
IF ( d > 31, d - 31, d )
VAR EasterMonth =
IF ( d > 31, 4, 3 )
RETURN
DATE ( EasterYear, EasterMonth, EasterDay ) //
-- End of code adapted from original VB version from https://www.assa.org.au/edm
VAR HolidayDate =
SWITCH (
TRUE,
[DayNumber] <> 0
&& [WeekDayNumber] <> 0, ERROR ( "Wrong configuration in HolidayParameters" ),
[DayNumber] <> 0
&& [MonthNumber] <= 12, DATE ( HolidayYear, [MonthNumber], [DayNumber] ),
[MonthNumber] = 99, -- Easter offset
EasterDate + [DayNumber],
[WeekDayNumber] <> 0,
VAR ReferenceDate =
DATE ( HolidayYear, 1
+ MOD ( [MonthNumber] - 1 + IF ( [OffsetWeek] < 0, 1 ), 12 ), 1 )
- IF ( [OffsetWeek] < 0, 1 )
VAR ReferenceWeekDayNumber =
WEEKDAY ( ReferenceDate, 1 ) - 1
VAR Offset =
[WeekDayNumber] - ReferenceWeekDayNumber
+ 7 * [OffsetWeek]
+ IF (
[OffsetWeek] > 0,
IF ( [WeekDayNumber] >= ReferenceWeekDayNumber, - 7 ),
IF ( ReferenceWeekDayNumber >= [WeekDayNumber], 7 )
)
RETURN
ReferenceDate + Offset,
ERROR ( "Wrong configuration in HolidayParameters" )
)
VAR HolidayDay =
WEEKDAY ( HolidayDate, 1 ) - 1
VAR SubstituteHolidayOffset =
IF (
[SubstituteHoliday] > 0
&& NOT CONTAINS ( WorkingDays, [WorkingDayNumber], HolidayDay ),
VAR NextWorkingDay =
MINX (
FILTER ( WorkingDays, [WorkingDayNumber] > HolidayDay ),
[WorkingDayNumber]
)
VAR SubstituteDay =
IF (
ISBLANK ( NextWorkingDay ),
MINX ( WorkingDays, [WorkingDayNumber] ) + 7,
NextWorkingDay
)
RETURN
SubstituteDay - HolidayDay
+ ( [SubstituteHoliday] - 1 )
)
RETURN
ROW (
-- Use DATE function to get a DATE column as a result
"HolidayDate", DATE ( YEAR ( HolidayDate ), MONTH ( HolidayDate ), DAY ( HolidayDate ) ),
"SubstituteHolidayOffset", SubstituteHolidayOffset
)
) //
VAR HolidayDates_RawDatesUnique =
DISTINCT (
SELECTCOLUMNS (
HolidayDates_GeneratedRawWithDuplicates,
"HolidayDateUnique", [HolidayDate]
)
)
VAR HolidayDates_GeneratedRaw =
GENERATE (
HolidayDates_RawDatesUnique,
VAR FilterDate = [HolidayDateUnique]
RETURN
TOPN (
1,
FILTER (
HolidayDates_GeneratedRawWithDuplicates,
[HolidayDate] = FilterDate
&& [SubstituteHoliday] = 0 -- Remove duplicates only if no substitute holidays
),
[ConflictPriority],
ASC,
[HolidayName],
ASC
)
)
