topic Re: Power BI - Rolling average for every month in DAX Commands and Tips

Power BI - Rolling average for every month

yogiceg — Mon, 11 Nov 2024 09:15:35 GMT

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

Re: Power BI - Rolling average for every month

Poojara_D12 — Mon, 11 Nov 2024 09:22:29 GMT

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

Re: Power BI - Rolling average for every month

bhanu_gautam — Mon, 11 Nov 2024 09:23:25 GMT

yogiceg , Try using

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

Re: Power BI - Rolling average for every month

yogiceg — Mon, 11 Nov 2024 09:50:04 GMT

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

Re: Power BI - Rolling average for every month

Anonymous — Tue, 12 Nov 2024 08:34:15 GMT

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.

Re: Power BI - Rolling average for every month

bhanu_gautam — Tue, 12 Nov 2024 08:26:07 GMT

yogiceg , PFA PBIX file

Re: Power BI - Rolling average for every month

divyed — Tue, 12 Nov 2024 14:48:54 GMT

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