Hi,

I'm seeing a consistent and baffling problem with the calculations of FY (across several different organizations).  Has anyone else seen this, can you explain the behavior, or propose a work-around?

The problem is that FISCAL_YEAR() returns a value off by one.

To reproduce the problem:

1. Double-check your FY settings.  Let's say Sept-Aug for simplicity, but this holds across FY.  FY is named by Ending Month.
2. With these settings, today must be in FY 2012 - we are between Sept 2011 and August 2012, which is FY 2012.
3. Create a new closed opportunity, with a close date of today.  Logic compels that the opportunity must be in the current fiscal year, which per (2) is 2012.
4. Note the Opportunity ID.  Open up the Developer Console, paste the following code, and change the opp ID accordingly:

AggregateResult[] ar = [SELECT FISCAL_YEAR(CloseDate) fy FROM Opportunity

WHERE Id = '006A000000GjODf' GROUP BY FISCAL_YEAR(CloseDate)];

System.debug('FISCAL YEAR IS: ');

System.debug(ar.get(0).get('fy'));

1. Check the output - it displays a FY of 2011 instead of 2012.
2. I'm seeing this consistent pattern:  FISCAL_YEAR() is returning one fiscal year too low for all opportunities.

Has anyone else seen this, can you explain the behavior, or propose a work-around?