Create a Calendar Table in Power BI using DAX functions

Calendar table is frequently used to perform Time Intelligence functions and in many scenario we need calendar table.

If you don’t have any date table in your data model, you can easily create date table using some DAX functions.

Following these steps to create a calendar table-

Step 1: Create table, Go to Modelling tab and click to table

Create table

Step 2:  Write below DAX to create Calendar table with all necessary columns.

Calendar Table =

ADDCOLUMNS (

CALENDAR ( DATE ( 2019, 1, 1 ), DATE ( 2020, 12, 31 ) ),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q"
& FORMAT ( [Date], "Q" )
)

Here, we pass manual start & end date under Calendar DAX function, as you know Calendar DAX function return the table with single column name “[Date]”

So, same [Date] column name we will pass in others dax functions.

We used ADDCOLUMN DAX function to add other columns with date column, like:-

• FORMAT ( [Date], “YYYYMMDD” )
• YEAR ( [Date] )
• FORMAT ( [Date], “MM” ) etc.

Create a Calendar table to using existing dataset date column:

• Change only Start & End date under Calendar Dax.
• Pass MIN date as a Start date & Max date as a End date

CALENDAR ( MIN(Orders[Order Date]), MAX(Orders[Order Date]))

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.