Most data warehouses store date columns as integers for query performance reasons. For example, an Order Date Key column on a fact table would store the 20180225 (YYYYMMDD) value as an integer data type to represent February 25th, 2018. Likewise, an existing date dimension table in the data warehouse usually also contains a YYYYMMDD date key column to support the join to these fact tables in SQL queries. If this date dimension table also contains a date column and meets essential data integrity criteria, the Mark as Date Table feature in Power BI Desktop can be used to leverage existing integer/whole number columns representing dates for relationships.

In the following screenshot, the Date table has been selected in the Fields list in Power BI Desktop and the Mark as Date Table icon has been selected from the modeling tab of the ribbon:

As shown in the preceding screenshot, the column named Date, which is stored as a Date data type, has been specified as the Date column to use by the Mark as Date Table feature. Power BI validates that this column meets the required criteria to function properly.

In addition to relationships based on YYYYMMDD columns, this feature enables DAX Time Intelligence functions, such as SAMEPERIODLASTYEAR(), to work properly. Power BI will use the date column specified by the model author in the Mark as Date Table setting in executing these expressions.

To utilize the Mark as Date Table feature, the Date column (Date data type) specified for the Mark as Date Table feature must meet the following criteria:

• No null values.
• No duplicate values.
• Contiguous date values:
  • There must be a single date value for each date from the earliest date to the latest date. In other words, there can't be any gaps or missing dates.
• If a date/time column is used, the timestamp must be the same for each value of the column.