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]
* [Opp Cost/Hr]
and one for each reference period.
example for the previous 3 years
[Date1] >=DATEADD('year',-3,[Start Date])
THEN [Unscheduled downtime]
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
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!