[Power BI] The Importance of a Dynamic Calendar Table

DAX function: CALENDARAUTO( )

CALENDARAUTO() is my favorite DAX function when it comes to creating a dynamic calendar table. It generates a continuous date table from the beginning to the end of a year.(1/1/XXXX to 12/31/XXXX)

Create the Calendar Table

Assuming we have the product data from 2/1/2015 to 3/31/2021, let’s imagine what the date range is with CALENDARAUTO. Yes, you are correct. The date range will be from 1/1/2015 to 12/31/2021.

  • VAR MaxYear = YEAR ( MAX ( ‘Product Data’[Date]) )
  • ADDCOLUMNS (
    FILTER (
    CALENDARAUTO( ),
    AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
    ),
    “Year”, YEAR ( [Date] )
    )
The DAX codes above for a Calendar table
How a Calendar Table looks

Add More Columns to the Calendar Table

Now that we have the calendar table, I would suggest that we can add some more columns to the table to make it more user-friendly.

  • Month Short = FORMAT(‘Calendar Table’[Date],”mmm”)
  • Month Index = FORMAT(‘Calendar Table’[Date],”mm”)
  • Week Number = WEEKNUM(‘Calendar Table’[Date])
  • DoW Index = WEEKDAY(‘Calendar Table’[Date])
  • DoW Name = FORMAT(‘Calendar Table’[Date],”dddd”)
  • DoW Short = FORMAT(‘Calendar Table’[Date],”ddd”)
  • Day Number= DAY(‘Calendar Table’[Date])

Relate the Calendar Table to the Product table

This is the very last and the most important step. If you don’t relate the calendar table to the main table (Product table), everything would not work. Therefore, don’t forget to relate the calendar table.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store