Creating 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 a date table to using some DAX functions.

Let’s get started-

Following these steps in order to create a Calendar table-

Step-1: Under Modeling Tab > Click on Table icon.

Step-2: Now write a DAX to create a calendar table. It will returns a table with a single column named “Date” that contains a contiguous set of dates.

Calendar table=
CALENDAR(DATE( 2021, 1, 1 ), DATE( 2021, 12, 31 ) )

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

Calendar table =

ADDCOLUMNS(
CALENDAR(DATE( 2021, 1, 1 ), DATE( 2021, 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" )
)

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]))

Refer more Power BI Post – Power BI

Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome.