Forum Discussion
Helper IRolling average 30 day
Dear all, I need some help here.
I want to create a 30 day rolling average. I have a daily sales record In column C and I want to average every 30 days like column D
How can I do it in Power Bi?
if you need more information please tell me
Thank you all
Busno
Hi Busno ,
If you need to calculate according to the calendar, it should be like this.
DATESINPERIOD() function takes into account dates that do not exist in your table.
Measure = CALCULATE( AVERAGE(Sheet3[C]), DATESINPERIOD( Sheet3[A], MAX(Sheet3[A]), -30, DAY ), ALL(Sheet3) )If you need to calculate according to the number of days in the table, it should be like this.
Measure 2 = CALCULATE( AVERAGE(Sheet3[C]), FILTER( ALL(Sheet3), Sheet3[Index] <= MAX(Sheet3[Index]) && Sheet3[Index] >= MAX(Sheet3[Index]) - 29 ) )Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
Busno , With a date table
Rolling 30 = CALCULATE(Average(Sales[Sales record]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-30,DAY))
or
Rolling 30 = CALCULATE(Average(Sales[Sales record]),DATESINPERIOD('Date'[Date ],MAX('Date'[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.
BusnoDear, Amitchandak
I already try you DAX but the result not the same as excel I attached.
Thank you for your help
Busno
- v-lionel-msft
Community Support
Hi Busno ,
If you need to calculate according to the calendar, it should be like this.
DATESINPERIOD() function takes into account dates that do not exist in your table.
Measure = CALCULATE( AVERAGE(Sheet3[C]), DATESINPERIOD( Sheet3[A], MAX(Sheet3[A]), -30, DAY ), ALL(Sheet3) )If you need to calculate according to the number of days in the table, it should be like this.
Measure 2 = CALCULATE( AVERAGE(Sheet3[C]), FILTER( ALL(Sheet3), Sheet3[Index] <= MAX(Sheet3[Index]) && Sheet3[Index] >= MAX(Sheet3[Index]) - 29 ) )Please refer to my .pbix file.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
