Forum Discussion
Rolling Average Distinct count question
thomsontam I wrote a series of blog posts on DAX Performance Optimization
https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275
https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-2/ba-p/976813
Have you used the Performance Analyzer?
There is a series of best practices here for optimization:
https://maqsoftware.com/expertise/powerbi/dax-best-practices
There is great learning module available here:
https://docs.microsoft.com/en-us/learn/modules/optimize-model-power-bi/
Other than that, not sure I can assist. Didn't mean to link spam but performance optimization in DAX is a thick subject and would need your dataset to be specific!
thomsontam , I am not sure it can give you a performance gain. But create a date table and join it with you table and do this calculation based on date table
try
Rolling 90 = CALCULATE(sum(table1[Unit]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-90,DAY)) //or 89
or
CALCULATE((Sum(table1[Unit]))/90,
DATESBETWEEN(Date[Date],
MAX(Date[Date]) - 89 ,
MAX(Date[Date]))
Or like This
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.