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 a 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 ( 2025, 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 provide manual start and end dates as inputs to the Calendar DAX function. As you are aware, the Calendar DAX function generates a table containing a single column named “[Date]”.

So, we will use the same [Date] column name in other 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.