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:
- Double-check your FY settings. Let's say Sept-Aug for simplicity, but this holds across FY. FY is named by Ending Month.
- With these settings, today must be in FY 2012 - we are between Sept 2011 and August 2012, which is FY 2012.
- 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.
- 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: ');
- Check the output - it displays a FY of 2011 instead of 2012.
- 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?