Forum Discussion
DAX Year to date measure, Time Intelligence performance vs using offsets performance
I’m hoping that the community can help me to clarify YTD performance vs using calendar table offsets.
I have always heard that TI (time intelligence) functions perform badly but from what I'm seeing this does not appear to be the case.
I'm calculating financial year to date using the following 2 measures:
1st the TI version
All Crimes YTD Ti =
CALCULATE( [All Crimes], DATESYTD( DimDate[Date], "30/6" ) )
Then the alternative no ti version:
All Crimes YTD No Ti =
VAR __max_date = MAX( DimDate[Date] )
VAR __fyear = SELECTEDVALUE( DimDate[FYear] )
VAR __tbl =
FILTER(
ALL( DimDate[Date], DimDate[FYear] ),
DimDate[Date] <= __max_date && DimDate[FYear] = __fyear
)
VAR __sumx = CALCULATE( [All Crimes], __tbl )
RETURN
__sumx
my work uses a 445 calendar so it's necessary to use offsets for any calendar calculations.
From my test file, a typical performance analyzser result will be as such:
These results of the no ti version being about 200 ms slower then the ti version are consistent across multiple tests.
The pbix file is here.
The file also contains another "no ti" measure that I got Gemini to create, but it performed the worst out of all of them, so I didn't include the code above.
To be clear, the measures I'm testing are the 2 shown in the code blocks above.
I haven't got into performance optimisation much up until now so I'd be intersted to hear about why the no ti measure runs slower.
And the bigger question is, what's the verdict on TI functions in relation to performance as in this case, Ti seems to be doing quite well!
Many thanks,
Tim
Hi timosborn,
the thing was that TI calculations runs pretty slow in DirectQuery (but this is not so true anymore). TI is definitely better than any alternative most of the times as Microsoft has optimized those functions.
So my suggestion after 14 years of DAX is to go ahead with the schema CALCULATE + built-in tie intelligence functions like DATESYTD all the time you can
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
4 Replies
- FBergamaschi
Super User
Hi timosborn,
the thing was that TI calculations runs pretty slow in DirectQuery (but this is not so true anymore). TI is definitely better than any alternative most of the times as Microsoft has optimized those functions.
So my suggestion after 14 years of DAX is to go ahead with the schema CALCULATE + built-in tie intelligence functions like DATESYTD all the time you can
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
many thanks for the reply
- Shahid12523
Community Champion
- Time Intelligence (TI) functions like DATESYTD() are often faster because they're internally optimized by the DAX engine.
- Your No TI version uses FILTER, ALL, and manual logic, which adds overhead and slows things down.
- Despite the myth, TI functions can outperform custom offset logic—especially when your fiscal calendar aligns well (like using "30/6").
- For 445 calendars, offsets may still be necessary, but TI is surprisingly efficient when applicable.thank you for the explanation.
