cancel
Showing results for
Did you mean:

## Use DAX to create a calendar table with holidays

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 =
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

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

Top Kudoed Posts
Latest Articles
Archives
Polls
What is your favorite Power BI Feature release this month?