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>)| Argument | Required | Description |
|---|---|---|
| start_date | Required | The first date of the table. |
| end_date | Required | The 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