DAX is tough as it is, but when time intelligence functions are introduced into the mix things get complicated quick. I constantly hear things like:
Unable to use time intelligence Time intelligence is not working Time intelligence works on one model but not another.
01
The main point of issue, in my opinion, is that time intelligence functions obfuscate (or altogether hide) the underlying functions being performed in a measure. For example, there is an implicit REMOVEFILTERES( 'Date Table'[Date] ) on added to time intelligence functions.
02
The second issue is that time intelligence functions perform differently depending on your date table configuration. For example, if you have a complete calendar (Jan to Dec) or partial calendar (Jan to current date) calculations such as year over year growth will be completely different for the current year.
03
The third issue is that there are many time intelligence functions similar in name, but different in function. For example, PARALLELPERIOD() and DATESINPERIOD(), PREVIOUSYEAR() and SAMEPERIODLASTYEAR(), etc.
04
Lastly, time intelligence functions materialize all dates in your date table. While this isn't a problem in small models, this can become problematic in larger models. Furthermore, the impact to performance grows exponentially as your measure grow more complex.
What's the alternative?
If you are performing calculations at the individual day level, continuing with time intelligence is actually your best bet. For however confusing they are, time intelligence is actually quite good at day level metrics, and the algorithm it employs covers you for most of what you would ever need. I.e. You do not need to consider the different amount of days across different months, and even leap years are accounted for.
However, if you mostly work with monthly or yearly calculations, working with a contiguous year-month index column will vastly simplify the calculations. This column can be created by taking the year number, multiplying by 12, and adding the month number to the result.
Year Month Index = 'Date Table'[Year Number] * 12 + 'Date Table'[Month Number]Previous month calculations go from this:
CALCULATE(
[Total Sales],
PREVIOUSMONTH( 'Date Table'[Date] )
)To this:
CALCULATE(
[Total Sales],
REMOVEFILTERS( 'Date Table' ),
'Date Table'[Year Month Index] =
MAX( 'Date Table'[Year Month Index] ) - 1
)Note: We need the REMOVFILTERS to ensure that whatever date columns you have on your visual do not interfere with the measure.
We can compare the performance of these measures over date and products and see the index measure is almost twice as fast as time intelligence!
Time intelligence performance (905 ms):
Index performance (449 ms):
The performance gains we realize are from the amount of rows materialized. You can see that the time intelligence measure materializes 873K rows (line 6), whereas the index measure only materializes 118K rows (line 8).
Rolling aggregations
Rolling periods become simplified as well. Instead of using DATESINPERIOD or PARALLELPERIOD or DATEADD or some combination of them, we just use the Year Month Index column:
Rolling 6 Month Sales =
VAR _MaxYearMonth = MAX( 'Date Table'[Year Month Index] )
RETURN
CALCULATE(
[Total Sales],
REMOVEFILTERS( 'Date Table' ),
'Date Table'[Year Month Index] > _MaxYearMonth - 6,
'Date Table'[Year Month Index] <= _MaxYearMonth
)Not only does this technique shine with import mode, Direct Query models benefit greatly as well. SQLBI has an excellent book, Optimizing DAX where they describe this technique along with many (many!) more tips and tricks. They also have an excellent article on multiple ways to do date based calculations without using time intelligence, following the same technique described here in addition to others.
Cheers!
Comments