Power BI - Rolling average for every month

[yogiceg]

Hi Experts,

Kindly assist how to make rolling average in power BI for the below excel made data. Need to make visualization with rolling average. Advance thanks.

Year-Month	Number of items	Sales value	Average per month	Rolling average
2024-01	20	180	9.00	9.00
2024-02	29	242	8.34	8.67
2024-03	19	164	8.63	8.66
2024-04	29	240	8.28	8.56
2024-05	15	114	7.6	8.37
2024-06	9	62	6.89	8.12
2024-07	24	347	14.46	9.03
2024-08	10	87	8.7	8.99
2024-09	8	55	6.88	8.75
2024-10	14	92	6.57	8.53
2024-11	5	16	3.20	8.05

[divyed]

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

[bhanu_gautam]

@yogiceg , PFA PBIX file

[Anonymous · Accepted solution]

Hi @yogiceg ,

You can try formula like below:

MEASURE =
AVERAGEX (
    FILTER (
        ALL ( 'SalesData' ),
        'SalesData'[Year-Month] <= MAX ( 'SalesData'[Year-Month] )
    ),
    [Average per month]
)

Best Regards,
Adamk Kong

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

[yogiceg]

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

[bhanu_gautam]

@yogiceg , Try using 

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

[Poojara_D12]

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