8 Replies Latest reply on Jun 26, 2018 9:49 AM by george dudley

    Apply 4-month Average Full Year of Monthly Data

    george dudley

      Hello,

      I have been tasked with creating a simple line graph comparing Sales to Budget.  The problem is that we don't have actual budget data, and the powers that be want to use the average of the first four months of the year and apply that as a constant monthly value for the full year.

       

      I need to create a calculated field that will create that constant value.  Our data has month-year values, so I wrote a calc called Month Integer that pulls the Datepart (month) to use in the budget calculation.  Then I tried calculating the budget by using "If [Month Integer]>0 AND [Month Integer] <5 Then ([Sales]/4) END."  There are two reasons that doesn't work.  One is that it only graphs for Jan-April, and the other is that it doesn't create a constant value.  Basically I need to know how to make this appear as a reference line based on the Jan-Apr average that will adjust when filtered for Client, Region, etc.

       

      I have attached a sample workbook based on some simplified dummy data.