CALENDAR
A table of one column, [Date], with every date from a start date to an end date.
Group: Date and time. Compatible with DAX.
Syntax
CALENDAR ( <start_date>, <end_date> )| Parameter | Type | Description |
|---|---|---|
start_date | Date | The first date of the table. |
end_date | Date | The last date of the table; it must not come before the start. |
Returns
Table. One column, [Date], with a row for every day from the start to the end, both included.
Remarks
- The time of day of either end is dropped: each row is a date at midnight.
- A start after the end is an error, and so is a blank start or end.
- The usual use is a date table of the model's own:
ADDCOLUMNS ( CALENDAR ( ... ), "Year", YEAR ( [Date] ) )in a calculated table, its range chosen to cover the model's facts. - Built from the facts, as
CALENDAR ( MIN ( Sales[Month] ), EOMONTH ( MAX ( Sales[Month] ), 0 ) ), the table grows with the data; written withDATE, it stays as given.
Examples
Five days around a leap day
EVALUATE
CALENDAR ( DATE ( 2024, 2, 27 ), DATE ( 2024, 3, 2 ) )| Date |
|---|
| 2024-02-27 |
| 2024-02-28 |
| 2024-02-29 |
| 2024-03-01 |
| 2024-03-02 |
Days in two years
2024 is a leap year.
EVALUATE
ROW (
"2024", COUNTROWS ( CALENDAR ( DATE ( 2024, 1, 1 ), DATE ( 2024, 12, 31 ) ) ),
"2025", COUNTROWS ( CALENDAR ( DATE ( 2025, 1, 1 ), DATE ( 2025, 12, 31 ) ) )
)| 2024 | 2025 |
|---|---|
| 366 | 365 |
A date table that spans the sales
From the first sales month to the end of the last one.
EVALUATE
VAR Days =
CALENDAR ( MIN ( Sales[Month] ), EOMONTH ( MAX ( Sales[Month] ), 0 ) )
RETURN
ROW ( "First", MINX ( Days, [Date] ), "Last", MAXX ( Days, [Date] ), "Days", COUNTROWS ( Days ) )| First | Last | Days |
|---|---|---|
| 2023-11-01 | 2026-10-31 | 1096 |