A calendar table stores a sequence of unique dates between a start date and an end date. For simplicity in relations among tables, it is sensible to create a calendar table for each date column you would like to include in visualizations.
Say you have the table “account” with column creation date. To create a calendar table:
-
From Data Editor, go to Table Tools > New tables.
-
On the header bar, use CALENDAR() and pass starting creation date and ending creation date as inputs.
account calendar = CALENDAR ( MIN ( account[creation date] ), MAX ( account[creation date] ) )The previous piece of DAX code creates the table “account calendar” with unique dates between the earliest and latest dates found in the column creation date.
-
Mark the new table as a calendar table. Go to Table tools > Mark as date table > Mark as date table.
-
For a Date column, select the column Date and click Ok.
Within calendar tables, you can further create other useful columns such as calendar week, month, semester and year.
