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

Get certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started

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