Home » Power BI » Creating Calendar Table in Power BI using DAX Functions

Creating Calendar Table in Power BI using DAX Functions

Date 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.

DAX Table

DAX Table




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 ) )
Calendar Table

Calendar Table

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" )
)
Complete calendar table

Complete calendar table



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.



Leave a Reply