
1. Re: Annualize YTD Value and Compare vs. Multi Yr Average
Michel Caissie Sep 27, 2018 10:10 AM (in response to David Roscoe)David,
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])
AND
[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)]
END
see the final result on Sheet 3
Michel

2. Re: Annualize YTD Value and Compare vs. Multi Yr Average
David Roscoe Sep 27, 2018 2:49 PM (in response to Michel Caissie)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 1Aug 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!