topic Re: 3 months rolling average display for each month in DAX Commands and Tips

3 months rolling average display for each month

kyen27 — Fri, 07 Mar 2025 04:39:18 GMT

Hello,

I have a data table with something that looks like this.

and I want to create a measure that will allow me to create a matrix table that display the 3 month rolling average for each month, that looks something like this:

I have tried so many formulas looking at similar posts and even asking AI but no luck. I keep getting the same result where each period column displays the average of all transactions in its own month. or where it would display the sum of each month and the grand total would display the average of the latest 3 months of the entire data set.

any help is appreciated.

Thanks,

Re: 3 months rolling average display for each month

bhanu_gautam — Fri, 07 Mar 2025 05:28:08 GMT

kyen27 Create a measure

DAX
3MonthRollingAvg =
CALCULATE(
AVERAGEX(
DATESINPERIOD(
'Table'[Period],
LASTDATE('Table'[Period]),
-3,
MONTH
),
'Table'[Amount]
)
)

Re: 3 months rolling average display for each month

vivek31 — Fri, 07 Mar 2025 06:14:31 GMT

HI kyen27 ,

you can try this formula

3M moving avg 2 = var period = DATESINPERIOD('Calendar'[DateKey],MAX('Calendar'[DateKey]),-3,MONTH) var sales = CALCULATE([total_sales],period) VAR month_ = CALCULATE(DISTINCTCOUNT('Calendar'[MonthOfYear]),period) RETURN DIVIDE(sales,month_)

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

Re: 3 months rolling average display for each month

kyen27 — Fri, 07 Mar 2025 14:13:42 GMT

Hello,

thank you for your response but i dont have a date column.

Re: 3 months rolling average display for each month

kyen27 — Fri, 07 Mar 2025 14:14:39 GMT

Hello, i get an error saying this: