Calculating moving average

[Philip-K]

Hi,

I'm trying to calculate the moving average over the past year for every month. I have formed a table with 4 columns:

Jaarmaand = year+"-"+month

Aantal oorzaken = count of a certain event (this is the column I want the moving average over)

Datum = Date(left(jaarmaand;4);right(jaarmaand;2);1) (this is the first day of every month calculated for sake of the next column)

Moving average =
                               CALCULATE(
                                      AVERAGE('Summarize aantal oorzaken'[Aantal oorzaken]);
                                      DATESINPERIOD(
                                                   'Summarize aantal oorzaken'[Datum];
                                                    LASTDATE('Summarize aantal oorzaken'[Datum]);
                                                    -12;
                                                   MONTH
                                                               )
                                                   )

The problem is that the Moving average column only displays the value for that date, not the moving average. The aim is to average the left selected cells into the right selected cell. Any help is greatly appreciated!

[jmdh]

Hi,  For me, this works:

Assuming a table with columns Date and Amount, the calculated column below does the job :

Amount MAVG =

var sdate=Min(Amounts[Date])+365

var d = 'Amounts'[Date]
var d365 =d-365

var r=
IF(d>=sdate;
 SUMX(  FILTER('Amounts';  Amounts[Date] <= d  && Amounts[Date] > d365);
  [Amount]   )
 /12
)
RETURN
r

[Philip-K]

Hi jmdh,

Thank you for the help. The solution does work, but I am left with two questions you might be able to answer:

1. Does this account for leap years?

2. With this solution, you count the average over all of 2014 (for example) and post it in januari 2015. I would like to have it post to december of 2014. How would you go about that in this solution?

Kind regards,

Philip

[jmdh · Accepted solution]

Hi Philip,

Glad it works for you.
regarding your question 2, just remove 31 days to sdate : this is sdate that contains the start date of the first result.

regarding 1, there is a function EDATE that should do it:

Amount MAVG2b =
var sdate=Min(Amounts[Date])+365-31 // This is the month when to start displaying results (used below in IF)
var d = 'Amounts'[Date]  // the current date
var d365 =EDATE(d,-12)  // this calculates a date 12 months before (i assume it works with leap years)
var r=
IF(d>=sdate,
 SUMX(
  FILTER('Amounts',  Amounts[Date] <= d  && Amounts[Date] > d365),
  [Amount]
  )
 /12  
)
RETURN
r

[Philip-K]

Works perfectly, thanks alot!

Made one small adjustment: used "averagex" in stead of "sumx && /12"