Specify Mark as Date Table for use with time intelligence

APPLIES TO: yesSQL Server Analysis Services yesAzure Analysis Services yesPower 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. For example:

    a. |Date|
    b. |----------|
    c. |7/1/2010 12:00:00 AM|
    d. |7/2/2010 12:00:00 AM|
    e. |7/3/2010 12:00:00 AM|
    f. |7/4/2010 12:00:00 AM|
    g. |7/5/2010 12:00:00 AM|

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

See also

Time intelligence Functions (DAX)