Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

## 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:

PBIX as attached.

Author: Jay Wang

Reviewer: Kerry and Ula

Comments
Top Kudoed Posts
Latest Articles
Archives