Forum Discussion
rolling average last 30 days
Dear All
I have been trying to calculate the rolling average 30 days for every day. I have used below query which is working for all excep the minimum date in the data base
For ex: we have may month data then below query work fine for all days except 1st may on 1st may the value should be zero but it is showing average of same day(1stmay) for rest other days it is working fine. can you please support
3 Replies
- Mariusz
Community Champion
Hi Anonymous
DATESINPERIOD is a time intelligence function, therefore, it requires date dimension or auto DateTime hierarchy to work properly, you can start by using one or another and is if it fixes the issue.
It's important that if you use the date dimension it contains the -31 days as well otherwise the result might be not what you expect.
try this article for the ref.
Best Regards,
Mariusz
If this post helps, then please consider Accepting it as the solution.
Please feel free to connect with me.
LinkedIn - amitchandak
Super User
Anonymous , try like this example with date calendar
Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-30,Day))
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.- Above query got reslved by this below query.Averagelast30days =VAR tday= LASTDATE('KPI TABLE'[DATE])returnIF(ISBLANK(CALCULATE(AVERAGE('KPI TABLE'[COMPLETED_ORD_COUNT]),ALLEXCEPT('KPI TABLE','KPI TABLE'[BUSINESS_SERVICE],'KPI TABLE'[DIMENSION],'KPI TABLE'[DATE]),'KPI TABLE'[DATE]<tday &&'KPI TABLE'[DATE]>=tday-31)),0,CALCULATE(AVERAGE('KPI TABLE'[COMPLETED_ORD_COUNT]),ALLEXCEPT('KPI TABLE','KPI TABLE'[BUSINESS_SERVICE],'KPI TABLE'[DIMENSION],'KPI TABLE'[DATE]),'KPI TABLE'[DATE]<tday &&'KPI TABLE'[DATE]>=tday-31))
