3 Replies Latest reply on Nov 13, 2018 3:17 PM by Don Wise

    Previous Dynamic Average Calc With Parameter


      Hi All,



      I am trying to calculate Average of dynamic months entered as Parameter. I am trying with LOD to calculate Average but not not getting it right.


      What I am trying to do is,


      1. In sheet Quantity-2 , We see the months 5/1/2017-7/1/2017 because I have selected the Date as 8/31/2017 and and entered the Parameter value as 3.

      2. So, Past 3 months is displayed for 8/31/2017. Average sum(quantity) for three months is 287.33 .

      3. I am using below calc to fix the average value for the past months for any selected Date range.


      { FIXED :

      AVG({ FIXED : SUM( IF  [Date slice Filter] >= Dateadd( [Select Period], -[Enter The Period To Cacl Avg], DATETRUNC([Select Period],[Date Selection]))

      and [Date slice Filter]< dateadd( [Select Period],-1,[Date Selection]) then

         [Quantity] end) })}


      4. With this calculation , I am getting average as 862 and not 287.33.


      What am I missing here?