Hello everybody!
I am working on our individual calendar.
I have set up the basic columns like week, month, year etc.
I am following this structure:
Calendar =
VAR BaseCalendar =
CALENDAR ( DATE ( 2016; 1; 1 ); DATE ( 2025; 12; 31 ) )
RETURN
GENERATE (
BaseCalendar;
VAR BaseDate = [Date]
VAR Year =
YEAR ( BaseDate )
VAR MonthNumber =
MONTH ( BaseDate )
VAR WeekNumber =
WEEKNUM ( BaseDate )
VAR FWeek =
WEEKNUM ( BaseDate; 21 )
RETURN
ROW (
"Day"; BaseDate;
"Year"; Year;
"Month Number"; MonthNumber;
"Month"; FORMAT ( BaseDate; "mmmm" );
"Year Month"; FORMAT ( BaseDate; "yyyy-mm" );
"Day of Week"; FORMAT ( BaseDate; "dddd" );
"Day of Week Short"; FORMAT ( BaseDate; "ddd" );
"Week"; WeekNumber;
"Year-Week"; Year & "-" & WeekNumber;
"Fiscal Week"; FWeek;
"Fiscal Month Short"; SWITCH (
TRUE ();
FWeek IN { 1; 2; 3; 4 }; "Jan";
FWeek IN { 5; 6; 7; 8 }; "Feb";
FWeek IN { 9; 10; 11; 12; 13 }; "Mar";
FWeek IN { 14; 15; 16; 17 }; "Apr";
FWeek IN { 18; 19; 20; 21 }; "May";
FWeek IN { 22; 23; 24; 25; 26 }; "Jun";
FWeek IN { 27; 28; 29; 30 }; "Jul";
FWeek IN { 31; 32; 33; 34 }; "Aug";
FWeek IN { 35; 36; 37; 38; 39 }; "Sep";
FWeek IN { 40; 41; 42; 43 }; "Oct";
FWeek IN { 44; 45; 46; 47 }; "Nov";
FWeek IN { 48; 49; 50; 51; 52; 53 }; "Dec"
)
)
Now I would like to add a column for the holidays that is based on the sqlbi guys calendar.
They are following an approach like this, that I would like to add:
VAR HolidayParameters =
DATATABLE (
"ISO Country"; STRING;
-- ISO country code (to enable filter based on country)
"MonthNumber"; INTEGER;
-- Number of month - use 99 for relative dates using Easter as a reference
"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, ...
"OffsetDays"; INTEGER;
-- days to add after offsetWeek and WeekDayNumber have been applied
"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
{
-- DE = Germany
{ "DE"; 1; 1; 0; 0; 0; "New Year's Day"; 0; 100 };
{ "DE"; 99; -2; 0; 0; 0; "Good Friday"; 0; 50 };
{ "DE"; 99; 1; 0; 0; 0; "Easter Monday"; 0; 50 };
{ "DE"; 5; 1; 0; 0; 0; "Labour Day"; 0; 100 };
{ "DE"; 99; 39; 0; 0; 0; "Ascension Day"; 0; 50 };
{ "DE"; 99; 50; 0; 0; 0; "Whit Monday"; 0; 50 };
{ "DE"; 10; 3; 0; 0; 0; "German Unity Day"; 0; 100 };
{ "DE"; 12; 25; 0; 0; 0; "Christmas Day"; 0; 100 };
{ "DE"; 12; 26; 0; 0; 0; "St. Stephen's Day"; 0; 100 }
}
)
My question is, how can I include this approach in my dax calendar?
