Past 12 Months sales vs rolling average.

[siddhantk989]

Hi,

   I have sales data in which I have sales from year 2012 to current date. What I want to plot on a line graph is past 12 month sales vs past 12 month moving average sales. 

  I was able to calculate past 12 month sales but got stuck at past 12 month moving average.

  By 12 month moving average I mean that for current month (May 201) I should get the average for past 12 months ,i .e., May (2016) to April 2016. Similarly for April 2017 it should be average of sales between April 2016 to march 2017 and so on.

  Please help.

Thanks,

Siddhant

[v-sihou-msft · Accepted solution]

@siddhantk989

To calculate the moving 12 months average, you just need to have the moving 12 months total divide existing months in past 12 months. You can use DATESINPERIOD() to get the past 12 months context.

I assume you already have a YearMonth column in your table. You can try following formula:

Moving Average =
CALCULATE (
    SUM ( 'Table'[Amount] ) / DISTINCTCOUNT ( 'Table'[YearMonth] ),
    DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -12, MONTH )
)

Please also refer to blogs below:

Rolling 12 Months Average in DAX

Moving Averages, Sums, Etc.

Regards,