cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
taimaizumi
Helper I
Helper I

How to get moving average on monthly

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
>
taimaizumi_0-1666950809012.png


<Table>
Sales

taimaizumi_1-1666950839812.png

 

Calendar

taimaizumi_2-1666950862904.png

 

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


10 REPLIES 10
taimaizumi
Helper I
Helper I

@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
Helper I
Helper I

@mangaus1111 

Thanks a million!

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

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

taimaizumi
Helper I
Helper I

@mangaus1111 

I wrote this measure as below accordingto your advise

taimaizumi_0-1666957507505.png


But, result is not as I expected.

taimaizumi_1-1666957556257.png


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

Is your model not like this?

mangaus1111_0-1666958599784.png

 

mangaus1111
Solution Sage
Solution Sage

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
Helper I
Helper I

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
Solution Sage
Solution Sage

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
Helper I
Helper I

Hi @mangaus1111 

Thank you for you quick response.

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

taimaizumi_0-1666951993367.png

 



mangaus1111
Solution Sage
Solution Sage

Hi @taimaizumi ,

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

Helpful resources

Announcements
May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Submit your Data Story

Data Stories Gallery

Share your Data Story with the Community in the Data Stories Gallery.

Top Solution Authors