Generates a date table as a calculated table: given a start and end date, this DAX function returns a single column, called Date, listing every day in between, including both ends.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains CALENDAR in context, with comparison tables and the common traps.
Terms in this definition
- Date table
Lets you slice and group facts by period. It lists every calendar day of the years it covers exactly once, with nothing skipped, and classic DAX time intelligence only works after it has been marked as a date table.
- Calculated table
Instead of coming from a data source, this kind of model table is defined by a DAX formula. It's always held in Import mode and recomputed on each refresh, which makes it handy for staging intermediate results or duplicating a table for role-playing.
- DAX
Short for Data Analysis Expressions, the formula language you write measures, calculated columns and row-level security rules in, within tabular semantic models in Power BI, Excel Power Pivot or Analysis Services.
Related terms
- Calendar-based time intelligence
A preview approach to DAX date maths: after switching on Enhanced DAX Time Intelligence, you set up calendars on a table through Calendar options and pass those, rather than a date column, to time functions. Fiscal, retail and week-based calendars are all handled.
- CALENDARAUTO
Scans the model for its first and last dates and returns a continuous Date column covering complete calendar years, or fiscal years when you pass a year-end month. As later dates arrive through refresh, this DAX function's range grows to include them.
- Calendars.ReadWrite
Microsoft Graph permission for creating, reading, updating and deleting events; as an application permission it spans every calendar in the organisation and needs admin consent, while delegated it reaches only the signed-in user's own calendars.
- Calendars.ReadWrite.Shared
Delegated Graph permission reaching any calendar the signed-in user has access to, shared and delegate calendars included, but never the whole organisation.
- Classic time intelligence
The original DAX time functions, like TOTALYTD, which work from the date column of a table marked as a date table. They expect a Gregorian (or shifted Gregorian) calendar with no gaps in the dates and offer no week-level functions, which sets them apart from the calendar-based preview approach.
- ENDOFYEAR
Gives back the last day of the year in context. It can work from a date column, optionally with a custom year-end date, or from a calendar when calendar-based time intelligence is in use.
- FIRSTDATE
Given a date column or a calendar, this DAX time intelligence function returns the earliest date visible in context. Because the result is a single-row table, you can drop it in anywhere a date is expected.
- Mark as date table
Telling Power BI Desktop which table acts as the calendar, by choosing a date column with no gaps, blanks or duplicates. Classic time intelligence depends on this whenever relationships join on something other than a date, and doing it drops the automatic date/time tables.