Specify Mark as Date Table for use with time intelligence
Applies to: SQL Server Analysis Services Azure Analysis Services Power BI Premium
In order to use time intelligence functions in DAX formulas, you must specify a date table and a unique identifier (datetime) column of the Date data type. Once a column in the date table is specified as a unique identifier, you can create relationships between columns in the date table and any fact tables.
When using time intelligence functions, the following rules apply:
When using DAX time intelligence functions, never specify a datetime column from a fact table. Always create a separate date table in your model with at least one datetime column of Date data type and with unique values.
Make sure your date table has a continuous date range.
The datetime column in the date table should be at day granularity (without fractions of a day).
You must specify a date table and a unique identifier column by using the Mark the Date Table dialog box.
Create relationships between fact tables and columns of Date data type in the date table.
Specify a date table and unique identifier
In Visual Studio, in the model designer, click the date table.
Click Extensions > Table > Date > Mark as Date Table.
In the Mark as Date Table dialog box, in the Date listbox, select a column to be used as a unique identifier. This column must contain unique values and should be of Date data type.
If necessary, create any relationships between fact tables and the date table.