cancel
Showing results for
Did you mean:
Helper I

## Moving monthly average by Item

Hi all,

I have a table with the list of items and sales by month. How do I calculated monthly rolling average (3 month) by Item? I am trying to create a calcultated column. The example of data is show below

I found a few threads suggesting using AVERAGEX and DATESINPERIOD using calendar table. This does work but doesn't give me data by Part ID.

 Part ID Month Sales Moving month average (3M) AAA1 Jan 110 AAA1 Feb 100 AAA1 Mar 90 AAA1 Apr 77 AAA1 May 555 AAA2 Jan 200 AAA2 Feb 100 AAA2 Mar 222 AAA2 Apr 350 AAA2 May 110
1 ACCEPTED SOLUTION
Community Support

My sample table:

I build a Month table and related two tables by month columns.

Calculated column:

``MonthNo = RELATED('Month'[MonthNo])``
``````Moving Month Average (3M) =
AVERAGEX (
FILTER (
'Table',
'Table'[MonthNo] <= EARLIER ( 'Table'[MonthNo] )
&& 'Table'[MonthNo]
>= EARLIER ( 'Table'[MonthNo] ) - 2
&& 'Table'[Part ID] = EARLIER ( 'Table'[Part ID] )
),
'Table'[Sales]
)``````

Result is as below.

Best Regards,

Rico Zhou

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

4 REPLIES 4
Community Support

My sample table:

I build a Month table and related two tables by month columns.

Calculated column:

``MonthNo = RELATED('Month'[MonthNo])``
``````Moving Month Average (3M) =
AVERAGEX (
FILTER (
'Table',
'Table'[MonthNo] <= EARLIER ( 'Table'[MonthNo] )
&& 'Table'[MonthNo]
>= EARLIER ( 'Table'[MonthNo] ) - 2
&& 'Table'[Part ID] = EARLIER ( 'Table'[Part ID] )
),
'Table'[Sales]
)``````

Result is as below.

Best Regards,

Rico Zhou

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

Helper I

I havent tested it yet, but it looks like exactly what I was looking for. Thank you!

Helper I

Hi Amit

It does work for a measure but not for a calculated column

Super User

@rigosakhx , Try like this example with date table

Rolling 3 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH), not(isblank((Sales[Sales])))))

or

CALCULATE(Average(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-12,MONTH))

Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Announcements

#### The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

#### Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!