It is common to have the need to perform time-driven analysis on your data. For example, you may need to compare this month’s sales with the sales from the same period a year ago or you may need to calculate the number of days between two dates. Power BI provides a set of Time Intelligence functions that make it easy to perform these types of calculations. But to take advantage of the Time Intelligence functions in Power BI, you must have a date table in your Power BI model and to have that you will need to “mark” a table as a date table.

To mark a table as a date table, it must meet the following criteria:

• It must have a column of data type “Date” or “Date/time”. This will be referred to as the “date column”.
• The date column must contain unique date values. For example, you cannot have the value “3/1/2022” (or any other date value) listed twice (or more than once) in the table.
• The date column must not contain BLANKs or NULLs. Only unique date values should be in the table’s date column.
• The date column must not have any missing dates or gaps in dates. For example, you cannot have 1/1/2022 and then 1/3/2022 and not have 1/2/2022 in the date values.
• The date column must span full years. Keep in mind that a year isn’t necessarily a calendar year (January-December), but should cover the entire 12 months, such as all dates between July 1, 2022 – June 30, 2023, inclusive. If you have less than one year’s dates in your table, then the range can be less than a year, but there cannot be any gaps. Also,

Once these rules are met, you can then mark the table as the date table. To do this, you can either right-click on the table in the Data pane and select “Mark as date table” (as shown below):

Or with the table selected in the Data pane, from the Table Tools tab, click on the “Mark as date table” icon. This icon will be grayed out if there are no date columns in the table.

The “Mark as date table” dialog opens (which includes a warning), from which you can turn on the “Mark as date table” flag.

Turn on the flag, and then select / set the date column from the dropdown.

Power BI will then validate your data to make sure all the criteria is met for the chosen column. If you get an error, make the necessary changes and try again.

Thanks for reading!