The April 2017 update to Power BI introduced a ground breaking feature: Quick Measures. This article discusses the Quick Measures that deal with time intelligence.
Currently, there are six time intelligence Quick Measures, which can be divided into two categories — Absolute and Change %:
|Year||Year-to-date total||Year over year change|
|Quarter||Quarter-to-date total||Quarter over quarter change|
|Month||Month-to-date total||Month-over-month change|
The names of these Quick Measures are quite self-explanatory, and I won't go into detail here. Instead, I would like to focus on the current limitation of time intelligence Quick Measures: they have to use Power BI’s built-in date hierarchies. Power BI team is looking to remove the restriction in the future. Meanwhile, I'll discuss the current implementation of time intelligence Quick Measures.
Enter built-in Power BI date hierarchies
By default, Power BI creates date hierarchies for every date or date/time type column in your data model. These hidden tables, not visible to the naked eye, have out of box properties like Month and Quarter. They look similar to the following:
Here is a simplified procedure that Power BI follows when generating these hierarchies:
- Create a one-column calendar table in the following manner (apart from table/column names, this is the exact formula used by Power BI):
'Date' = Calendar(Date(Year(MIN('TableWithDateColumn'[DateColumn])), 1, 1), Date(Year(MAX('TableWithDateColumn'[DateColumn])), 12, 31))
What this formula does is Populates the 'Date'[Date] column with dates between the first day of the year corresponding to the earliest date and until the last day of the year corresponding to the latest date in the 'TableWithDateColumn'[DateColumn]
- Create the following calculated columns (again, these are the exact formulas Power BI uses):
- Day = DAY([Date])
- Month = FORMAT([Date], "MMMM")
- MonthNo = MONTH([Date])
- QuarterNo = INT(([MonthNo] + 2) / 3)
- Quarter = "Qtr " & [QuarterNo]
- Year = YEAR([Date])
- Power BI then sorts [Month] by [MonthNo] and [Quarter] by [QuarterNo]
- Finally, Power BI creates the date hierarchy:
Afterwards, you can use the column variations, such as 'Table'[Date].[Day]:
These give you exactly what you expect -- you can use the column values without seeing them in the model. These automatically created columns can be called in Power BI similarly to how you would call a function, but you still can't see these columns in the data model.
Using the built-in calendar hierarchies with Quick Measures
There are a few requirements that must be met in order to make the time intelligence Quick Measures work:
- You should have either:
- One to many relationship from Calendar to Fact table
- Date or datetime column in the fact table
- If you are using a separate Calendar table, you should have either:
- Single direction relationship
- Contiguous selection of dates in calendar
So if your Quick Measures won't define or break, check whether the above conditions are met.
Removing the built-in date tables
As you may have noticed, the built-in hierarchies contain whole years only. What are the implications of this? If you have a single-row table that has a datetime type column (think "Last Refreshed" type of table), then Power BI will still build a date hierarchy for it that has 365 or 366 rows. If you have a lot of date or date/time type columns, and the range of dates is many years, then your data model file size will suffer considerably. Unfortunately, you need these built-in hierarchies if you want to use the time intelligence Quick Measures.
If you write your own Time Intelligence measures, then you don't need the built-in calendars, and this feature can be easily turned off. Just go to File → Options and settings → Options:
(Once you disable the built-in calendars, you can't use time intelligence Quick Measures.)
I prefer building my own calendar tables and disabling the built-in calendars, because you have more flexibility this way, and your model does not get bloated.
Using Quick Measures and writing your own measures is kind of like driving with automatic or manual transmission; the former is easier, but you can do far more with the latter :-)