Use DAX to create a calendar table with holidays

[V-lianl-msft]

Scenario:
When calculating in the date dimension, sometimes we only want to calculate the Data in workday. In that case, we will need to get rid of holidays or weekends. This blog will show you how to create a calendar table and mark the holidays, weekends and workday.

Preparations:
To get the holiday and weekend calendar table, we will need a universal calendar table and a table contains holidays.
According to below DAX formula, we can get a universal calendar table.

Calendar =
VAR dates =
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) )
VAR date_table_base =
ADDCOLUMNS (
dates,
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"MonthNO", MONTH ( [Date] ),
"YearMonth", FORMAT ( [Date], "YYYYMM" ),
"WeekNO", WEEKNUM ( [Date] ),
"WeekDay", WEEKDAY ( [Date], 2 )
)
RETURN
date_table_base

Then we can get holiday table from internet and load it to Power BI. Here I take Chinese holidays in 2021 as an example.

Operations:
Use LOOKUPVALUE() function to create holiday column on calendar table.

Column = LOOKUPVALUE(Holidays[Name],Holidays[Date],'Calendar'[Date])

Use IF() function to create to create working day function calendar table.

working day =
SWITCH (
TRUE (),
ISBLANK ( 'Calendar'[holidays] )
&& 'Calendar'[WeekDay] < 6, "working day",
ISBLANK ( 'Calendar'[holidays] )
&& 'Calendar'[WeekDay] > 5, "weekend",
"holidays"
)

Below are the results:

PBIX as attached.

Author: Jay Wang

Reviewer: Kerry and Ula

Use DAX to create a calendar table with holidays (1).pbix

[Anonymous]

This is great. I have never thought about using LOOKUPVALUE() and a holiday table. I would say the only deficiency with this approach is that you have to constantly be updating the holiday table for future values. It gets even more complicated for those of us in the public sector who have holidays and holiday-observation days.

While it is much more time consuming and tedious, it would be a better approach in the long run to codify the conditions of holidays (and holiday-observation days) in the date dimension table. My organization had a clear policy of when holidays were observed, so I was able to use those conditions through a SWITCH function. I have posted an example below:

[To be used as one of the column constructors in a date-dimension table.]

"COD Observed Holidays",
    SWITCH(
        TRUE(),
        -- New Year's Day (including weekend observations)
        //// When falling on Saturday, move to preceding Friday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 31 && WEEKDAY( [Date], 1 ) = 6, "New Year's Day (Observed)",
        //// When falling on Sunday, move to following Monday
        MONTH( [Date] ) = 1 && DAY( [Date] ) = 2 && WEEKDAY( [Date], 1 ) = 2, "New Year's Day (Observed)",
        //// When falling on normal workday, do not shift holiday observation
        MONTH( [Date] ) = 1 && DAY( [Date] ) = 1 && WEEKDAY( [Date], 1 ) IN { 2, 3, 4, 5, 6 }, "New Year's Day",
        -- Martin Luther King's Birthday
        MONTH( [Date] ) = 1 && DAY( [Date] ) IN { 15, 16, 17, 18, 19, 20, 21 } && WEEKDAY( [Date], 1 ) = 2, "Martin Luther King's Birthday",
        -- Memorial Day
        MONTH( [Date] ) = 5 && DAY( [Date] ) IN { 25, 26, 27, 28, 29, 30, 31 } && WEEKDAY( [Date], 1 ) = 2, "Memorial Day",
        -- Independence Day (including weekend observations)
        //// When falling on Saturday, move to preceding Friday
        MONTH( [Date] ) = 7 && DAY( [Date] ) = 3 && WEEKDAY( [Date], 1 ) = 6, "Independence Day (Observed)",
        // -- When falling on Sunday, move to following Monday
        MONTH( [Date] ) = 7 && DAY( [Date] ) = 5 && WEEKDAY( [Date], 1 ) = 2, "Independence Day (Observed)",
        //// When falling on normal workday, do not shift holiday observation
        MONTH( [Date] ) = 7 && DAY( [Date] ) = 4 && WEEKDAY( [Date], 1 ) IN { 2, 3, 4, 5, 6 }, "Independence Day",
        -- Labor Day
        MONTH( [Date] ) = 9 && DAY( [Date] ) IN { 1, 2, 3, 4, 5, 6, 7 } && WEEKDAY( [Date], 1 ) = 2, "Labor Day",
        -- Thanksgiving
        MONTH( [Date] ) = 11 && DAY( [Date] ) IN { 22, 23, 24, 25, 26, 27, 28 } && WEEKDAY( [Date], 1 ) = 5, "Thanksgiving",
        -- Friday after Thanksgiving
        MONTH( [Date] ) = 11 && DAY( [Date] ) IN { 23, 24, 25, 26, 27, 28, 29 } && WEEKDAY( [Date], 1 ) = 6, "Friday after Thanksgiving",
        -- Christmas Eve (including weekend observations)
        //// When falling on Saturday, move to preceding Friday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 23 && WEEKDAY( [Date], 1 ) = 6, "Christmas Eve (Observed)",
        //// When falling on Friday, move to preceding Thursday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 23 && WEEKDAY( [Date], 1 ) = 5, "Christmas Eve (Observed)",
        //// When falling on Sunday, move to following Monday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 25 && WEEKDAY( [Date], 1 ) = 2, "Christmas Eve (Observed)",
        //// When falling on normal workday (except Friday), do not shift holiday observation
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 24 && WEEKDAY( [Date], 1 ) IN { 2, 3, 4, 5 }, "Christmas Eve",
        -- Christmas Day (including weekend observations)
        //// When falling on Saturday, move to preceding Friday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 24 && WEEKDAY( [Date], 1 ) = 6, "Christmas Day (Observed)",
        //// When falling on Sunday, move to following Monday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 26 && WEEKDAY( [Date], 1 ) = 2, "Christmas Day (Observed)",
        //// When falling on Monday, move to following Tuesday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 26 && WEEKDAY( [Date], 1 ) = 3, "Christmas Day (Observed)",
        //// When falling on normal workday (except Monday), do not shift holiday observation
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 25 && WEEKDAY( [Date], 1 ) IN { 3, 4, 5, 6 }, "Christmas Day",
        -- Leave non-holidays blank
        BLANK()
)

[bstark1287]

I changed the calculated calendar table to dynamic and added New years Eve and Juneteenth to the observed holidays list. This thread was so helpful! Thank you @V-lianl-msft 

Calculated Calendar table: 

Calendar = 
VAR dates =
CALENDAR ( TODAY() - 730 , TODAY() + 1460 )
VAR date_table_base =
ADDCOLUMNS (
dates,
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"MonthNO", MONTH ( [Date] ),
"YearMonth", FORMAT ( [Date], "YYYYMM" ),
"WeekNO", WEEKNUM ( [Date] ),
"WeekDay", WEEKDAY ( [Date], 1 ),
"Day", FORMAT([Date],"DDDD")
)
RETURN
date_table_base

Holiday list: 

Holidays = 
    SWITCH(
        TRUE(),

        -- New Year's Day (including weekend observations)
        //// When falling on Saturday, move to preceding Friday
        MONTH( [Date] ) = 1 && DAY( [Date] ) = 3 && WEEKDAY( [Date], 1 ) = 2, "New Year's Day (Observed)",
        //// When falling on Sunday, move to following Monday
        MONTH( [Date] ) = 1 && DAY( [Date] ) = 2 && WEEKDAY( [Date], 1 ) = 2, "New Year's Day (Observed)",
        //// When falling on normal workday, do not shift holiday observation
        MONTH( [Date] ) = 1 && DAY( [Date] ) = 1 && WEEKDAY( [Date], 1 ) IN { 2, 3, 4, 5, 6 }, "New Year's Day",
        
        -- Martin Luther King's Birthday
        MONTH( [Date] ) = 1 && DAY( [Date] ) IN { 15, 16, 17, 18, 19, 20, 21 } && WEEKDAY( [Date], 1 ) = 2, "Martin Luther King's Birthday",

        -- Memorial Day
        MONTH( [Date] ) = 5 && DAY( [Date] ) IN { 25, 26, 27, 28, 29, 30, 31 } && WEEKDAY( [Date], 1 ) = 2, "Memorial Day",

        -- Juneteenth (including weekend observations)
        //// When falling on Saturday, move to preceding Friday
        MONTH( [Date] ) = 6 && DAY( [Date] ) = 18 && WEEKDAY( [Date], 1 ) = 6, "Juneteenth (Observed)",
        //// When falling on Sunday, move to following Monday
        MONTH( [Date] ) = 6 && DAY( [Date] ) = 20 && WEEKDAY( [Date], 1 ) = 2, "Juneteenth (Observed)",
        //// When falling on normal workday, do not shift holiday observation
        MONTH( [Date] ) = 6 && DAY( [Date] ) = 19 && WEEKDAY( [Date], 1 ) IN { 2, 3, 4, 5, 6 }, "Juneteenth",

        -- Independence Day (including weekend observations)
        //// When falling on Saturday, move to preceding Friday
        MONTH( [Date] ) = 7 && DAY( [Date] ) = 3 && WEEKDAY( [Date], 1 ) = 6, "Independence Day (Observed)",
        //// When falling on Sunday, move to following Monday
        MONTH( [Date] ) = 7 && DAY( [Date] ) = 5 && WEEKDAY( [Date], 1 ) = 2, "Independence Day (Observed)",
        //// When falling on normal workday, do not shift holiday observation
        MONTH( [Date] ) = 7 && DAY( [Date] ) = 4 && WEEKDAY( [Date], 1 ) IN { 2, 3, 4, 5, 6 }, "Independence Day",

        -- Labor Day
        MONTH( [Date] ) = 9 && DAY( [Date] ) IN { 1, 2, 3, 4, 5, 6, 7 } && WEEKDAY( [Date], 1 ) = 2, "Labor Day",

        -- Thanksgiving
        MONTH( [Date] ) = 11 && DAY( [Date] ) IN { 22, 23, 24, 25, 26, 27, 28 } && WEEKDAY( [Date], 1 ) = 5, "Thanksgiving",
        -- Friday after Thanksgiving
         MONTH( [Date] ) = 11 && DAY( [Date] ) IN { 23, 24, 25, 26, 27, 28, 29 } && WEEKDAY( [Date], 1 ) = 6, "Friday after Thanksgiving",

        -- Christmas Eve (including weekend observations)
        //// When falling on Saturday, move to preceding Friday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 23 && WEEKDAY( [Date], 1 ) = 6, "Christmas Eve (Observed)",
        //// When falling on Friday, move to preceding Thursday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 23 && WEEKDAY( [Date], 1 ) = 5, "Christmas Eve (Observed)",
        //// When falling on Sunday, move to following Monday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 25 && WEEKDAY( [Date], 1 ) = 2, "Christmas Eve (Observed)",
        //// When falling on normal workday (except Friday), do not shift holiday observation
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 24 && WEEKDAY( [Date], 1 ) IN { 2, 3, 4, 5 }, "Christmas Eve",

        -- Christmas Day (including weekend observations)
        //// When falling on Saturday, move to preceding Friday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 24 && WEEKDAY( [Date], 1 ) = 6, "Christmas Day (Observed)",
        //// When falling on Sunday, move to following Monday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 26 && WEEKDAY( [Date], 1 ) = 2, "Christmas Day (Observed)",
        //// When falling on Monday, move to following Tuesday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 26 && WEEKDAY( [Date], 1 ) = 3, "Christmas Day (Observed)",
        //// When falling on normal workday (except Monday), do not shift holiday observation
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 25 && WEEKDAY( [Date], 1 ) IN { 3, 4, 5, 6 }, "Christmas Day",

        -- New Year's Eve (including weekend observations)
        //// When falling on Saturday, move to preceding Friday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 29 && WEEKDAY( [Date], 1 ) = 6, "New Year's Eve (Observed)",
        //// When falling on Sunday, move to preceding Friday
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 30 && WEEKDAY( [Date], 1 ) = 6, "New Year's Eve (Observed)",
        //// When falling on normal workday, do not shift holiday observation
        MONTH( [Date] ) = 12 && DAY( [Date] ) = 31 && WEEKDAY( [Date], 1 ) IN { 2, 3, 4, 5, 6 }, "New Year's Eve",

        -- Leave non-holidays blank
        BLANK()
)

[pthapa]

For those who are interested different approach; here is what I have created for US.

//First I created two separate columns for checking Monday and Thursday.

//================================

//Step:1

Instance of Monday =

IF(

    'Calendar'[Weekday Number] = 1,

        RANKX(FILTER('Calendar','Calendar'[Weekday Number]=1 &&

            'Calendar'[Month] = EARLIER('Calendar'[Month]) && 'Calendar'[Year] = EARLIER('Calendar'[Year])),

                'Calendar'[Date],,ASC),BLANK())

//=================================

//Step:2

//Check for Thursday

Instance of Thursday =

IF(

    'Calendar'[Weekday Number] = 4,

        RANKX(FILTER('Calendar','Calendar'[Weekday Number] = 4 &&

            'Calendar'[Month] = EARLIER('Calendar'[Month]) && 'Calendar'[Year] = EARLIER('Calendar'[Year])),

                'Calendar'[Date],,ASC),BLANK())

//================================

//Step:3

Then I create a Holiday Name column:

Holiday Name =

SWITCH(

    TRUE(),

'Calendar'[Month] = 1 && 'Calendar'[Day] = 1,                    "New Year",

'Calendar'[Month] = 5 && 'Calendar'[Instance of Monday] = 4,     "Memorial Day",

'Calendar'[Month] = 7 && 'Calendar'[Day] = 4,                    "Independence Day",

'Calendar'[Month] = 9 && 'Calendar'[Instance of Monday] = 1,     "Labor Day",

'Calendar'[Month] = 11 && 'Calendar'[Instance of Thursday] = 4, "Thanksgiving Day",

'Calendar'[Month] = 12 && 'Calendar'[Day] = 25,                  "Christmas Day"

)

//================================

//Step:4

Is Holiday =

SWITCH(TRUE(),

'Calendar'[Holiday Name] <> "", "yes",

'Calendar'[Month] = 1 && 'Calendar'[Day] = 2 && 'Calendar'[Weekday Name] = "Monday", "yes", //Checking if 2nd Jan is Monday, then it will be holiday

'Calendar'[Month] = 7 && 'Calendar'[Day] = 5 && 'Calendar'[Weekday Name] = "Monday", "yes", //if July 5th is Monday, it will be holiday

'Calendar'[Month] = 12 && 'Calendar'[Day] = 26 && 'Calendar'[Weekday Name] = "Monday", "yes" //if Dec 26th is Monday, it will be holiday

)

//================================

Based on above columns, you can now have networking days:

Net Working Days =

var _firstDate = MIN('Calendar'[Date])

var _lastDate = MAX('Calendar'[Date])

var _isHoliday =

CALCULATETABLE(

    DISTINCT('Calendar'[Date]),

    'Calendar'[Is Holiday] = "yes"

)

RETURN

NETWORKDAYS(

    _firstDate,

    _lastDate,

    1,//this will exclude Saturday & Sunday

    _isHoliday

)

Hope this is helpful.
Let me know in comments.

Thanks,

PT

[pthapa]

One more column to add on above steps:

//========This is for Makeover Holidays=========

Updated Holiday Name =

IF('Calendar'[Is Holiday] = "yes" && 'Calendar'[Holiday Name] = "", "Makeover Holiday",

    'Calendar'[Holiday Name]

)

Thanks,

PT

[TJohnsonOK]

Hi pthapa,

I am trying to use your code--thank you so much.  However, there are sme dates that say is holiday of "yes" but does not show the Holiday Name--for example 1/2/2023 is a Monday, so New Years would be on that day.  However, on the line for Holiday Hame it is blank.  When I use your column for "Makeover Holiday", it shows "Makeover Holiday".  How can I get New Years Celebrated on the day it is a holiday for a busines in the Holiday Name column?  There are others, just wanted to use this as an example.   Thank you

[pthapa]

Hi TJohnsonOK,

These are custom columns so go ahead and add them as below.

//1-Is Holiday Column:

Is Holiday =

SWITCH(TRUE(),

'Calendar'[Holiday Name] <> "", "yes",

'Calendar'[Month] = 1 && 'Calendar'[Day] = 2 && 'Calendar'[Weekday Name] = "Monday", "yes", //Checking if 2nd jan is Monday, then it will be holiday

'Calendar'[Month] = 7 && 'Calendar'[Day] = 5 && 'Calendar'[Weekday Name] = "Monday", "yes", //if july 5th is Monday, it will be holiday

'Calendar'[Month] = 12 && 'Calendar'[Day] = 26 && 'Calendar'[Weekday Name] = "Monday", "yes", //if Dec 26th is Monday, it will be holiday

"no"

)

//2- Holiday Name Column:

Holiday Name =

SWITCH(

    TRUE(),

'Calendar'[Month] = 1 && 'Calendar'[Day] = 1,                    "New Year",

'Calendar'[Month] = 5 && 'Calendar'[Instance of Monday] = 4,     "Memorial Day",

'Calendar'[Month] = 7 && 'Calendar'[Day] = 4,                    "Independence Day",

'Calendar'[Month] = 9 && 'Calendar'[Instance of Monday] = 1,     "Labor Day",

'Calendar'[Month] = 11 && 'Calendar'[Instance of Thursday] = 4, "Thanksgiving Day",

'Calendar'[Month] = 12 && 'Calendar'[Day] = 25,                  "Christmas Day"

)

//3-Final Holiday Name column (So this one will be your final Holdiay name column)

Updated Holiday Name =

IF('Calendar'[Is Holiday] = "yes" && 'Calendar'[Holiday Name] = "", "Makeover Holiday",

    'Calendar'[Holiday Name]

)

If you follow above steps of creating three columns in order, your holiday name column will be the last one. You should get the Jan, 1st as : "New Year" on the Final Holiday Name column.

Let me know if this solves your concern.

Thanks,

PT

[TJohnsonOK]

@pthapa  Thank you so much for your quick reply.  I think that fixed all of them except for one for July 4th--the date is 7/4/2027--It shows as 7/5/2027--however, in the Updated Holiday Name column it still shows "Makeover Holiday"?  

Thanks

[pthapa]

I am glad that it is working for you now.

July 4, 2027 will show as Independence Day in the Updated Holiday Name column however, since Holiday is on Weekend, the following Monday which will be July 5th. will be considered as Makeover (observed) holiday for a lot of businesses.
You can change the Makeover holiday as per your business requirements.

Thanks,

PT