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.
VAR dates =
CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2021, 12, 31 ) )
VAR date_table_base =
"Year", YEAR ( [Date] ),
"Month", FORMAT ( [Date], "MMM" ),
"MonthNO", MONTH ( [Date] ),
"YearMonth", FORMAT ( [Date], "YYYYMM" ),
"WeekNO", WEEKNUM ( [Date] ),
"WeekDay", WEEKDAY ( [Date], 2 )
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.