rolling average last 30 days

[Anonymous]

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 

Averageoflast30days =

VAR tday= LASTDATE('KPI TABLE'[DATE])

VAR tday_2 = tday-1

return

IF(ISBLANK(CALCULATE(AVERAGE('KPI TABLE'[COMPLETED_ORD_COUNT]),ALLEXCEPT('KPI TABLE','KPI TABLE'[BUSINESS_SERVICE],'KPI TABLE'[DIMENSION],'KPI TABLE'[DATE]),DATESINPERIOD('KPI TABLE'[DATE],tday_2,-31,DAY))),0,CALCULATE(AVERAGE('KPI TABLE'[COMPLETED_ORD_COUNT]),ALLEXCEPT('KPI TABLE','KPI TABLE'[BUSINESS_SERVICE],'KPI TABLE'[DIMENSION],'KPI TABLE'[DATE]),DATESINPERIOD('KPI TABLE'[DATE],tday_2,-31,DAY)))

[amitchandak]

@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.

[Anonymous]

Above query got reslved by this below query.

Averagelast30days =

VAR tday= LASTDATE('KPI TABLE'[DATE])

return

IF(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))

[Mariusz]

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.

https://radacad.com/basics-of-time-intelligence-in-dax-for-power-bi-year-to-date-quarter-to-date-month-to-date

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn