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

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: