Power BI - Rolling average for every month

Hi yogiceg 

To create a rolling average in Power BI based on your data, follow these steps:

DAX:

Rolling Average = 
AVERAGEX(
    DATESINPERIOD(
        'Table'[Year-Month],
        LASTDATE('Table'[Year-Month]),
        -3,
        MONTH
    ),
    'Table'[Average per month]
)

Replace 'Table' with the actual name of your table in Power BI.

• DATESINPERIOD defines the date range for the rolling average (3 months).
• LASTDATE picks the latest date in the period.
• AVERAGEX calculates the average over the date range

You can adjust -3 to a different number of months (e.g., -6 for a 6-month rolling average).

• Go to Report view.
• Insert a Line Chart from the Visualizations pane.
• Set up the fields for the chart:
  • Drag Year-Month to the Axis.
  • Drag the new Rolling Average measure to the Values.
  • Optionally, add Average per month to compare both the actual monthly average and the rolling average.
• Format the Line Chart as needed (line color, thickness, etc.).
• Adjust the Date axis for clarity (ensure it's in chronological order).

This setup will display a rolling average that updates as each month is added to your data.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant

yogiceg , Try using 

Rolling Average =
CALCULATE(
AVERAGE('Table'[Average per month]),
DATESINPERIOD(
'Table'[Year-Month],
LASTDATE('Table'[Year-Month]),
-12,
MONTH
)
)

Thanks for the reply. but, i am not getting the actual results. 

yogiceg , PFA PBIX file

Hello yogiceg ,

Here is the dax for you.

Rolling_Average =

CALCULATE(

    AVERAGE('Test'[Avg_per_Month]),

    FILTER(

        ALL(Test[Year-Month]),

        Test[Year-Month] <= MAX(Test[Year-Month])

    )

)

 

 

I hope it solves your query. Please mark this as solution if this helps .

 

Cheers