Rolling average 30 day

[Busno]

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

[amitchandak]

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


Appreciate your Kudos.

[Busno]

Dear, 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 · Accepted solution]

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 Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.