3 months rolling average display for each month

[kyen27]

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,

[vivek31]

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.

[kyen27]

Hello,

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

[bhanu_gautam]

@kyen27 Create a measure

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

[kyen27]

Hello, i get an error saying this: