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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.