CALENDARAUTO
Returns a date table that spans from the earliest to the latest date found in the date columns of the model.
CALENDARAUTO([<fiscal_year_end_month>]), where the optional month is a number from 1 to 12Returns: A table with a Date column.
What it does
CALENDARAUTO scans the model for date columns, finds the earliest and latest dates, and returns a continuous list of days across that span. It saves time when a model is small and the dates are clean. The risk is that one stray date, such as a placeholder of 1900 or a typo of 2099, widens the table and affects every time intelligence result. For a model that will be used for real reporting, most teams prefer CALENDAR with explicit dates.
Syntax and arguments
- fiscal_year_end_month: the month number in which the fiscal year ends, from 1 to 12, for example 6 for a June 30 year end. Optional. Leave it out for a calendar year.
Example
A small sales model has no Calendar yet. The analyst wants a date table fast, with June as the fiscal year end month, to test a few measures.
Calendar = CALENDARAUTO(6)
The table covers the earliest to latest date found in the model's date columns. The analyst checks the first and last rows before using the table. If the range looks wrong, a stray date is the usual cause.
Example: a second, different use
A check that finds the actual first and last dates in the data, so the team can see whether a stray date has widened a calendar built by CALENDARAUTO.
Data First Date = MIN('Sales'[OrderDate])
Data Last Date = MAX('Sales'[OrderDate])
Things to watch
- A single bad date in any column extends the calendar to that date. Check the first and last rows after every refresh.
- CALENDARAUTO can include dates from columns that are not related to the sales data, so the table may be wider than the business needs.
- For a production model, build the table with CALENDAR and fixed start and end dates, and mark it as the date table.
- Turn off the automatic date and time option in the model settings, so that hidden date tables are not created for every date column.
In business terms
CALENDARAUTO is handy for a quick prototype, a one-off analysis or a small workbook. Finance reports that go to a board, a lender or an auditor should use a Calendar with fixed boundaries, so that the period totals do not change because of a single bad date.