Forum Discussion

Philip-K's avatar
Philip-K
Frequent Visitor
9 years ago
Solved

Calculating moving average

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!

 

Need Help
  • jmdh's avatar
    jmdh
    9 years ago

    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

4 Replies

  • jmdh's avatar
    jmdh
    Advocate IV
    9 years ago

    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's avatar
      Philip-K
      Frequent Visitor
      9 years ago

      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's avatar
        jmdh
        Advocate IV
        9 years ago

        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