2 Replies Latest reply on Sep 27, 2018 2:49 PM by David Roscoe

    Annualize YTD Value and Compare vs. Multi Yr Average

    David Roscoe

      Many of my use cases involve determining if the current value is normal/abnormal versus a longer time period than just the previous year or period (checking if the value is cyclical or not).  I'd like the ability to annualize a YTD value (in this case downtime) no matter a what point in the year and compare it against a longer time period say last 3 years. Our fiscal year runs 11/1-10/31.  I have a partial solution that I can compare a given "reporting" time period (i.e. 11/1/2017-8/31/2018) vs a "reference" time period like the previous period (month) or year but when I time to compare against the previous 3 year average it calculates incorrectly. That's because it's summing the entire period minus 3 (11/1/2015-8/31/2017) (Period calc) and if I change the Unschedule DT Opp Cost from SUM([Unscheduled downtime])* [Opp Cost/Hr] to AVG([Unscheduled downtime])* [Opp Cost/Hr] then it provides the average at the monthly grain. I'm open to a tweak to the scenario below or starting over from scratch. Please help, thanks!

      Period Calc

      WHEN "Previous 3 year Avg" THEN


      [Date1]>=[Start Date]


      [Date1]<=[End Date]

      THEN "Reporting period"


      [Date1]>=DATEADD('year',-3,[Start Date])


      [Date1]<=DATEADD('year',-0,[End Date])

      THEN "Reference period"

      ELSE "Other"




        • 1. Re: Annualize YTD Value and Compare vs. Multi Yr Average
          Michel Caissie



          The problem comes from the fact that your have overlapping data between the reporting period and the reference period.

          So in your Period calc, when a row returns true in the IF , it is flagged as a reporting period,  and is not checked in the  ELSEIF.


          A logic calculation cannot return 2 values for the same argument.


          So I suggest that you go with multiple calculations.


          One for the reporting period

          SUM( IF  [Date1] >=[Start Date] AND [Date1] <=[End Date]

          THEN[Unscheduled downtime]

          END )

          * [Opp Cost/Hr]


          and one for each reference period.

          example for the previous 3 years

          SUM( IF

          [Date1] >=DATEADD('year',-3,[Start Date])


          [Date1]<=DATEADD('year',-0,[End Date])

          THEN [Unscheduled downtime]

          END )

          *[Opp Cost/Hr]


          Next , you do an extra calc  to select the reference period according to the parameter

          CASE [Compare to]

          WHEN "Previous period" THEN [Unscheduled DT Opp Cost (ref Previous period)]

          WHEN "Previous year" THEN [Unscheduled DT Opp Cost (ref Previous year)]

          WHEN "Previous 3 year Avg" THEN [Unscheduled DT Opp Cost (ref Previous 3 years)]



          see the final result on Sheet 3



          • 2. Re: Annualize YTD Value and Compare vs. Multi Yr Average
            David Roscoe

            Thanks Michel! It looks like your calcs work if the 10 months of FY18 could be annualized then it would match the time periods/values of ENTIRE FY16 & FY17. Currently it's not apples to apples since FY18 is a partial year. Probably my fault since within the Period calc my Previous 3 Yr Avg portion isn't correct.


            With the your new calcs in the below table. 10 month for FY17 is actually $4,612,740 and 10 months for FY16 is $4,505,700. That's why I thought if the 10 months of FY18 could be annualized (multiplied into a years worth of data based on the monthly average for that partial year) it would be easier to compare with any year.  I'd like to annualize the data whether it's only been 1 months or 11 months of a partial year.


            There are 2 options right.

            1. Compare like periods (Nov 1-Aug 31) for each year. (my original worked fine for Previous Period but not for longer periods or averages of longer periods)

            2. Annualize partial year FY18 to compare against FY17 and FY16.

            OR another variation which I was hoping to accomplish.

            2a. Annualize partial year FY18 to compare against the AVERAGE of multiple previous years (FY17 and FY16).


            I know it's a tough one, thanks for looking into it!