CODEWITHSUNDEEP
excelpowerbidaxpowerpivot
Aaron
Aaron

Reputation: 331

DAX Calendar Dynamic Holidays Function

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.

Upvotes: 1

Views: 918

Answers (3)

Aaron
Aaron

Reputation: 331

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

Upvotes: 0

Alexis Olson
Alexis Olson

Reputation: 40224

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.

Upvotes: 1

Seymour
Seymour

Reputation: 3274

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.

Upvotes: 1

Related Questions