ANS-0020 · SAVED SEARCHES & FORMULAS
How to Calculate Fiscal Year Start Date in NetSuite Saved Searches for Non-Calendar Fiscal Years
This guide provides a custom formula to determine the start date of a fiscal year that does not align with the standard calendar year, specifically for Australian fiscal years.
Short answer
To calculate the fiscal year start date in a NetSuite saved search for a non-calendar fiscal year, use a custom formula. This formula dynamically determines the fiscal year's beginning based on the current date, accommodating fiscal years like Australia's (July 1st to June 30th).
Scenario
Users often need to report on transaction amounts within a specific fiscal year in NetSuite saved searches. A common challenge arises when the organization's fiscal year does not coincide with the standard calendar year, such as a fiscal year starting on July 1st and ending on June 30th. This discrepancy requires a custom approach to accurately define the fiscal year period within saved search criteria.
Solution
To accurately determine the fiscal year start date in a NetSuite saved search for a non-calendar fiscal year, a custom formula field can be utilized. This formula evaluates the current month to identify the correct fiscal year start. For example, to define a fiscal year that begins on July 1st and ends on June 30th, the following formula can be used:Case when to_number(to_char({today}, 'MM')) < 7 then to_date('1-JUL-'||to_char(add_months({today}, -12), 'YYYY'), 'dd-MON-yyyy') else to_date('1-JUL-'||to_char({today}, 'YYYY'), 'dd-MON-yyyy') endThis formula ensures that the correct fiscal year start date is returned, adjusting for whether the current date falls before or after the fiscal year's start month. This method is particularly useful when an organization operates with a single, consistent fiscal year definition.
Expert NetSuite Support
Need help with this NetSuite issue?
Saved Searches & Formulas consulting and configuration support