DAX Calendar Dynamic Holidays Function

1
votes

I am building our individual calendar for the whole company. The typical basic columns (week, month, year) are set up. I am using an approach like this:

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" )
            )
    )

Now I am looking for a dynamic function to add a column with our holidays. Most of our holidays are based on a fixed date like 25.12 or 26.12. The days around eastern are dynamic.

Does anyone have any idea for this? I don't want to use an additional table with all holiday dates.

3
excelpowerbidaxpowerpivot
Most design patterns use two tables, your standard calender and your holiday dates,then join between the two to flag a holiday date. I would outline why you don't want to have two tables in your use case, and deviating from established best practice - Jon

3 Answers

1
votes

I agree with Jonee, there cannot be a built-in approach to manage holidays because they change based on year, country and even state.

The best-practice is to keep track in an additional table of all the holidays for your company. The granularity of the table is the Date and might just contain the date of holidays.

Then, you join the Calendar with the Holiday and create a new attribute HOLIDAY_FL in the Calendar.

Finally, you know for every Date in your Calendar whether it is a holiday.

1
votes

Easter is defined as the first Sunday after the first full moon on or after March 21. In theory, you could define the computus dynamically, but I really don't recommend trying to write lunar cycle logic in DAX instead of having a fixed holiday date table.

That said, it's entirely possible to define a holiday date table as a VAR inside your calendar table definition so that you don't have another physical table in your model.

0
votes

Thanks for all your help and advice. This approach works:

Var for eastern

            VAR Eastern =
                FLOOR ( DATE ( Year; 3; MOD ( 18,37 * MOD ( Year; 19 ) - 6; 29 ) ); 7 ) + 29

Var for Holidays

        VAR IsHoliday =
            SWITCH (
                TRUE ();
                FORMAT ( BaseDate; "MMDD" ) = "0101"; TRUE();
                BaseDate = Eastern - 2; TRUE();
                BaseDate = Eastern + 1; TRUE();
                FORMAT ( BaseDate; "MMDD" ) = "0501"; TRUE();
                BaseDate = Eastern + 39; TRUE();
                BaseDate = Eastern + 50; TRUE();
                FORMAT ( BaseDate; "MMDD" ) = "1003"; TRUE();
                FORMAT ( BaseDate; "MMDD" ) = "1031"; TRUE();
                FORMAT ( BaseDate; "MMDD" ) = "1224"; TRUE();
                FORMAT ( BaseDate; "MMDD" ) = "1225"; TRUE()
            )