cancel
Showing results for 
Search instead for 
Did you mean: 
V-lianl-msft

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

 

V-lianl-msft_0-1615968516713.png

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

jay2.png


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:

jay3.png

 


PBIX as attached.

Author: Jay Wang

Reviewer: Kerry and Ula

 

Comments

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

 

Polls
What is your favorite Power BI Feature release this month?