Edit

Specify Mark as Date Table for use with time intelligence

  • Article

Applies to: SQL Server Analysis Services Azure Analysis Services Fabric/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

  1. In Visual Studio, in the model designer, click the date table.

  2. Click Extensions > Table > Date > Mark as Date Table.

  3. 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.

  4. If necessary, create any relationships between fact tables and the date table.

See also

Time intelligence Functions (DAX)