I recently had to figure out how to get the fiscal year from a date field. Surprisingly, there wasn’t an explanation and ‘copy and paste’ formula already on here (or I just couldn’t find it).
Here is my take on the challenge:
To use this -
The end of the fiscal year in this formula is June 30th. If you have a different year end, change the ‘06-30’ in the Fiscal Year formula.
I then hide the Year field and group by fiscal years. It is great for seeing historical trends.
Nice one Nathan. You can also use the following formula in a single field if you’re happy with just returning the digits of the fiscal year (without the ‘FY’).
IF((DATETIME_FORMAT(Date, 'MM'))>6,((DATETIME_FORMAT(Date, 'YY'))+1), ((DATETIME_FORMAT(Date, 'YY'))))
*or replace both 'YY’s with ‘YYYY’ if you want ‘2019’ rather than just ‘19’.