10 Replies Latest reply: Oct 18, 2013 4:55 AM by Tresesco B RSS

    Rolling 3 Months average

      Hey Folk,

       

      I have a requirment in which i have to show rolling 3 Months Average in a Bar chart.

      Logic: For each month, average should be of previous 3 months including that month.

      Example: If Month is Feb-11, then average against Feb in Bar chart should be of Dec-10, Jan-11 and Feb-11.

      Likewise it should happen for every month in the Chart in dimension.

       

      Here is the logic which I am trying to implement:

      =(sum([Total Doses])

      +Sum({<YearMonth ={"$(=Date(addmonths(YearMonth, -2),'MMM-YY'))"}>}[Total Doses])

      +Sum({<YearMonth ={"$(=(addmonths(Max(YearMonth), -1)))"}>}[Total Doses]))/3

       

       

      Hoping for solutions....!!!