Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.