Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 IDMonthSalesMoving month average (3M)
AAA1Jan110 
AAA1Feb100 
AAA1Mar90 
AAA1Apr77 
AAA1May555 
AAA2Jan200 
AAA2Feb100 
AAA2Mar222 
AAA2Apr350 
AAA2May110 
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    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.

    You can download the pbix file from this link: Moving monthly average by Item

     

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    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.

    You can download the pbix file from this link: Moving monthly average by Item

     

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , 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))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Amit

     

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