Downloads
DAXDate and timeIntermediate

CALENDAR

Returns a table with one column called Date that holds every day from the start date to the end date.

Syntax
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

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

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.

Related functions

All DAX functions · View as Markdown · DAX fundamentals