How to get moving average on monthly

[taimaizumi]

HI, all

I'd like to get monthly moving average by making measure as below, but it may not work well
Could you please help me

------------------------------------------------------------

<Measure>

ave_month = CALCULATE(AVERAGEX(crossjoin(VALUES('Calendar'[Yearmonth]),VALUES('Sales'[Prodcut])),'mea'[sum]),DATESINPERIOD('Calendar'[Date],MIN('Calendar'[Date]),-1,MONTH))

<Model>

<Table>
Sales

Calendar

------------------------------------------------------------

[taimaizumi]

@mangaus1111 

Sorry I was wrong

What I'd like to get is 12 (average of sum of April "10" and sum of May 14 )

[taimaizumi]

@mangaus1111 

Thanks a million!

Based on your sample, what I'd like to get is 5.5(average of April 5 and May 6)

[mangaus1111]

then in the end what you want is the moving average of a moving average

[taimaizumi]

@mangaus1111 

I wrote this measure as below accordingto your advise

But, result is not as I expected.

It seems that average is calculated on a daily basis not monthly.

[mangaus1111]

Is your model not like this?

[mangaus1111]

Hi @taimaizumi ,

try this measure

Moving Average Last 2 Months = 
VAR _MaxMonth = MAX('Date'[Year*Month])
RETURN
CALCULATE(AVERAGEX('Facts5','Facts5'[Sales]),
           'Date'[Year*Month]>= _MaxMonth -1 && 'Date'[Year*Month] <= _MaxMonth
)

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

[taimaizumi]

HI, @mangaus1111 

Sorry, my explanation was not enough
What I'd like to get is "moving" average among this month and previous month by month and product

[mangaus1111]

But if the granularity of your report is Product and Month, then you need a simple Averagex. 

Avg = AVERAGEX('Sales','Sales'[Sales])

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

[taimaizumi]

Hi @mangaus1111 

Thank you for you quick response.

Output image is below.
I'd like to get result by monthly and products.

[mangaus1111]

Hi @taimaizumi ,

in the row of the matrix do you want Day and Product or Month and Product?