DAX CALENDAR function

Creates a date table with one row per day between a start date and an end date.

CALENDAR is the simplest way to build a date table in DAX. Time intelligence functions need a table like this, with every day present and no gaps.

Syntax

CALENDAR(<start_date>, <end_date>)
ArgumentRequiredDescription
start_dateRequiredThe first date of the table.
end_dateRequiredThe last date of the table.

Returns: A table with one column, named Date.

Examples

Calculated on the free practice data (table financials), with [Total Sales] = SUM(financials[Sales]) and [Total Profit] = SUM(financials[Profit]).

1. How many days a calendar holds

Days = COUNTROWS(CALENDAR(DATE(2013, 1, 1), DATE(2014, 12, 31)))

Result: 730

Two whole years, 2013 and 2014, make 730 rows. In Power BI, you would create the table with New table and add Year and Month columns.

Try CALENDAR yourself

Change the formula and run it on the 700-row practice data. Free, with no signup.

Want an exercise that checks your answer? Free DAX practice

Good to know

  • Cover whole years, from 1 January to 31 December, so time intelligence works correctly.
  • After creating the table, use Mark as date table in Power BI and relate it to your fact table.
  • CALENDARAUTO is similar, but it finds the date range from your model.

Related functions

Official reference: CALENDAR function on Microsoft Learn