Rules / Performance

Remove auto-date table

warning REMOVE_AUTO-DATE_TABLE · ported · model layer · scope: Table, CalculatedTable

What it checks

Calculated tables generated by the Auto date/time option, whose names start with DateTableTemplate_ or LocalDateTable_.

Each finding names the table, as 'LocalDateTable_8f2c1b4e', so there is one per date column the option built a calendar for, plus the template table.

Example

Fires the rule

table Sales
	column 'Order Date'
		dataType: dateTime
		formatString: mm/dd/yyyy
		sourceColumn: OrderDate

	column Amount
		dataType: decimal
		summarizeBy: sum
		sourceColumn: Amount

table LocalDateTable_8f2c1b4e
	isHidden

	column Date
		dataType: dateTime
		formatString: mm/dd/yyyy
		sourceColumn: [Date]

	column Year
		dataType: int64
		summarizeBy: none
		sourceColumn: [Year]

	partition LocalDateTable_8f2c1b4e = calculated
		mode: import
		source = Calendar(Date(Year(MIN('Sales'[Order Date])), 1, 1), Date(Year(MAX('Sales'[Order Date])), 12, 31))

After the fix

table Sales
	column 'Order Date'
		dataType: dateTime
		formatString: mm/dd/yyyy
		sourceColumn: OrderDate

	column Amount
		dataType: decimal
		summarizeBy: sum
		sourceColumn: Amount

table Date
	dataCategory: Time

	column Date
		dataType: dateTime
		isKey
		formatString: mm/dd/yyyy
		sourceColumn: Date

	column Year
		dataType: int64
		summarizeBy: none
		sourceColumn: Year

Why it matters

With Auto date/time on, Power BI Desktop creates a hidden calculated date table for every date column in the model, each with its own year, quarter, month, and day hierarchy. A model with a dozen date columns carries a dozen hidden tables, all rebuilt on every refresh, and none of them can be extended with fiscal periods or holidays or shared between fact tables. One real date table does everything they do, once.

How to fix it

In Power BI Desktop, open File, Options and settings, Options, then Current file, Data Load, and clear Auto date/time. Desktop deletes the hidden tables and the variations that point at them when the file is saved, so the table LocalDateTable_... blocks leave the project's TMDL files on the next save. The option itself is recorded on the model as annotation __PBI_TimeIntelligenceEnabled, which Desktop writes and clears for you rather than a property to edit by hand, and deleting the table blocks yourself without clearing the option only invites Desktop to write them again. Then give the model a calendar of its own: add a date table, relate each fact table's date column to it, and mark it with Mark as date table under Table tools, which writes dataCategory: Time on the table and isKey on its date column. Report authors lose the automatic date hierarchy under each date column when the option goes off, so expect to rebuild the visuals that used one against the new table's Year, Quarter, and Month columns.

When to ignore it

There is no lasting exception. These tables are not something a modeler chose, they are what Power BI Desktop falls back to, and every one of them is replaced by the single calendar the model should have anyway. The one honest reason to leave a finding for now is sequencing: in a report with many visuals built on the automatic date hierarchies, clearing the option breaks all of them at once, so the calendar goes in first, the visuals move across, and the option goes off last. That is a plan with a date on it rather than a decision to ignore the rule.

To ignore this rule on one object, add annotation pbiplint.ignore = REMOVE_AUTO-DATE_TABLE under the object in its TMDL file. Power BI Desktop keeps the annotation. To turn the rule off for a whole project, set "REMOVE_AUTO-DATE_TABLE": "off" under rules in pbiplint.config.json.

Quirks

The table has to be a calculated table as well as carry the name. A loaded table someone called DateTableTemplate_Old is not reported.
A composite model's copy of an auto date/time table is not reported either: a LocalDateTable_ table whose entity partition reads, in DirectQuery mode, the table of that name in the Power BI semantic model or Analysis Services model the composite model extends. The copy is not a calculated table, so the source rule leaves it out too. The table it reads is calculated in the model it extends, where this rule reports it and where Auto date/time is turned off.
The name test is case-sensitive and is a prefix, not a substring, so localdatetable_1 is not reported and neither is a table whose name merely contains the prefix further in.
Only these two names are known. A hidden calendar built by hand, or by another tool under another name, is invisible to this rule.
DateTableTemplate_ is the template table Desktop keeps; LocalDateTable_ is one table per date column. Both prefixes are reported, so a model with many date columns collects a finding for each of its hidden calendars.

REDUCE_USAGE_OF_CALCULATED_TABLES reports every calculated table, so each table reported here is reported there as well, and clearing the option clears both.
DATE/CALENDAR_TABLES_SHOULD_BE_MARKED_AS_A_DATE_TABLE matches any table whose name contains date or calendar, that defines no calendar, and that is not marked as a date table, which these tables are not, so an auto-date table is reported there too until it is gone.
MODEL_SHOULD_HAVE_A_DATE_TABLE reports the model that has no calendar of its own, which is the state that leaves Auto date/time doing the work.