How to calculate fiscal year to date in Snowflake (fiscal year starting in April)

A fiscal year that runs from 1 April to 31 March is common: it is the government financial year in the UK, India and Japan, and many companies follow it. Fiscal year to date then starts on 1 April, not on 1 January, and in February it covers almost eleven months.

The trick is to move every date back three months, so that April becomes January. The calendar functions then give fiscal answers, and moving forward three months again turns them back into dates.

A small example

CREATE TEMP TABLE Sales (OrderDate DATE, Amount NUMBER(10,2));
INSERT INTO Sales VALUES
  ('2025-03-20', 70),  ('2025-04-02', 90),  ('2025-09-15', 120),
  ('2026-01-10', 100), ('2026-03-05', 150), ('2026-04-08', 80),
  ('2026-05-02', 120), ('2026-05-15', 50),  ('2026-05-20', 300);

The fiscal year of a date

SELECT OrderDate,
       DATEADD(month, 3, DATE_TRUNC('year', DATEADD(month, -3, OrderDate))) AS FiscalYearStart,
       TO_VARCHAR(YEAR(DATEADD(month, -3, OrderDate))) || '-' ||
         RIGHT(TO_VARCHAR(YEAR(DATEADD(month, -3, OrderDate)) + 1), 2) AS FiscalYear,
       QUARTER(DATEADD(month, -3, OrderDate)) AS FiscalQuarter
FROM Sales
ORDER BY OrderDate;

DATEADD(month, -3, ...) moves 1 April to 1 January and 31 March to 31 December. DATE_TRUNC('year') finds the start of that shifted year, and adding the three months back gives 1 April. So 20 March 2025 belongs to the fiscal year that started on 1 April 2024, and 2 April 2025 opens the next one.

The label names both calendar years, 2025-26, because companies disagree on a single number: some call April 2025 to March 2026 FY2025, after the year it starts in, others FY2026, after the year it ends in. For a numeric column, YEAR(DATEADD(month, -3, OrderDate)) gives the first convention, and adding 1 gives the second.

Fiscal year to date

WITH Params AS (
  SELECT AsOf,
         DATEADD(month, 3, DATE_TRUNC('year', DATEADD(month, -3, AsOf))) AS FiscalYearStart
  FROM (SELECT '2026-05-15'::DATE AS AsOf)   -- CURRENT_DATE() in a live report
)
SELECT p.FiscalYearStart, p.AsOf,
       COALESCE(SUM(s.Amount), 0) AS FiscalYTD
FROM Params p
LEFT JOIN Sales s
  ON  s.OrderDate >= p.FiscalYearStart
  AND s.OrderDate <  DATEADD(day, 1, p.AsOf)
GROUP BY p.FiscalYearStart, p.AsOf;

On 15 May the fiscal year is six weeks old, so fiscal YTD (250) is half the calendar YTD (500). With the reference date set to 5 March 2026, the fiscal year started on 1 April 2025 and fiscal YTD is 460, while calendar YTD on that day is 250.

To compare with the previous fiscal year, move the reference date back with DATEADD(year, -1, AsOf) and apply the same expression, as in how to compare YTD with the same period last year.

Fiscal quarters

With an April start, fiscal quarters begin on the same days as calendar quarters (1 April, 1 July, 1 October and 1 January); only their numbers change, and April to June is Q1. Fiscal quarter to date is therefore DATE_TRUNC('quarter', AsOf), as in how to calculate YTD, QTD and MTD.

That does not hold for every start month. A fiscal year that starts in February has quarters from February, May, August and November, and the same shift gives their start:

DATEADD(month, 1, DATE_TRUNC('quarter', DATEADD(month, -1, AsOf)))

Other start months

The shift is the number of months between January and the first month of the fiscal year: 3 for April, 6 for July, 9 for October. Use that number in both DATEADD calls of every expression above.

When fiscal periods appear in many reports, compute them once in a calendar table, with one row per day and its fiscal year, quarter and week, and join to it. Every report then uses the same definition. A date spine is the starting point for that table.