Understanding Dates in Power BI Quick Measures

As I explained here, Power BI introduced the highly anticipated Quick Measures feature to avoid writing DAX formula for common measures. The feature is still in preview, so make sure you enable it in Power BI Desktop from File, Options and Settings, Options, Preview Features. Many quick measures, such as time intelligence calculations (YTD, QTD, Period Over Period Change, etc.), rely on a date field. The date field could come from a Power BI-provided date hierarchy or a Date table.

Letting Power BI auto-generate date hierarchies is easy but dangerous and limiting. As Chris Webb explains here, Power BI-provided hierarchies could bloat the size of the data model. For example, by disabling them in data in a data model provided by a customer, I was able to reduce the data model size from 300 MB to 30 MB!

Instead, a best practice is to have a Date table. It gives you a control over what date periods you want to include. It also allows you to analyze several fact tables by date in a single visual.

Unfortunately, unlike Power Pivot or Tabular, currently you can’t mark a Date table in Power BI Desktop, even though everything is configured correctly in your model. Attempting to use a date field from a Date table when setting up a quick measure, results in this error.

A “primary date column” means a field of a Date type in a Date table marked as such. DAX time intelligence functions only work when several conditions are met by the date column, such as must be at date granularity, must be continuous, cannot have duplicate values, etc. Since PowerBI hasn’t verified that a user supplied date column meets all those conditions, it doesn’t allow users to define Quick Measures to prevent unexpected results if user is unaware that they cannot just pick any date column.

So why the teaser then? A primary date column will work if you import a model with marked Date table from Power Pivot or if you connect to Tabular. While waiting for Power BI to let us mark Date tables, the workaround is to use a Power BI-provided hierarchy (at least to get the formula), and then change the formula manually to reference the Date field in your Date table. For an example of how to do this, refer to my blog that I mentioned at the beginning. Just don’t give up on data tables!