CALENDAR
Returns a table with one column called Date that holds every day from the start date to the end date.
CALENDAR(<start_date>, <end_date>)Returns: A table with one column, Date.
What it does
CALENDAR builds a continuous list of dates, one row for each day, with no gaps. Every time intelligence function in DAX needs such a list, called a date table, to compare periods correctly. CALENDAR creates the list, and ADDCOLUMNS adds columns for year, quarter, month and fiscal labels. The result becomes the Calendar table that the rest of the model is related to.
Syntax and arguments
- start_date: the first date of the table. Required. Use a fixed date, such as the first day of the earliest fiscal year in the data.
- end_date: the last date of the table. Required. Use a date that covers the last fiscal year you will report on.
Example
A calculated table gives a Calendar with the columns used in the rest of this reference. It covers whole calendar years from 2020 to 2030 and adds a June 30 fiscal year label.
Calendar =
VAR MinDate = DATE(2020, 1, 1)
VAR MaxDate = DATE(2030, 12, 31)
RETURN
ADDCOLUMNS(
CALENDAR(MinDate, MaxDate),
"Year", YEAR([Date]),
"Quarter", "Q" & QUARTER([Date]),
"Month Number", MONTH([Date]),
"Month", FORMAT([Date], "MMMM"),
"Fiscal Year", "FY" & (YEAR([Date]) + IF(MONTH([Date]) >= 7, 1, 0))
)
After you create the table, mark it as the date table in the model, and relate its Date column to the date column of each fact table. The table contains one row for every day, so the time intelligence functions find no gaps.
Example: a second, different use
A calendar that covers exactly the years in the sales data, so no stray dates widen it.
Calendar =
CALENDAR(
DATE(YEAR(MIN('Sales'[OrderDate])), 1, 1),
DATE(YEAR(MAX('Sales'[OrderDate])), 12, 31)
)
Things to watch
- The table must cover every date in the fact tables. A sale dated outside the range will not join to the Calendar.
- Use whole years, so year-to-date and prior-year functions always see complete periods.
- A calculated table is built at refresh. If a new year of data arrives, extend the end date and refresh.
- The Date column must hold dates with no time part, and each date must appear once.
In business terms
A Calendar table is the foundation of every period report: month-end close, year to date, prior year comparisons, rolling 12 months and fiscal year reporting. Building it once with explicit dates, marked as a date table, prevents most of the date errors finance teams see in dashboards.