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.

Step 1: Create table, Go to Modelling tab and click to 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 passed manual start & end date under Calendar DAX function, as you know Calendar DAX function returns 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