How to calculate YTD, QTD and MTD in Snowflake

A period-to-date total adds up everything from the start of a period to a reference date, usually today. Year to date (YTD), quarter to date (QTD) and month to date (MTD) differ only in where the period starts, so a single pass over the table can return all three.

A small example

Seven orders: one from last year, five up to 15 May 2026 and one placed after that date.

CREATE TEMP TABLE Sales (OrderDate DATE, Amount NUMBER(10,2));
INSERT INTO Sales VALUES
  ('2025-12-30', 200),
  ('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 query

WITH Params AS (
  SELECT '2026-05-15'::DATE AS AsOf   -- CURRENT_DATE() in a live report
)
SELECT
  SUM(IFF(s.OrderDate >= DATE_TRUNC('year', p.AsOf),    s.Amount, 0)) AS YTD,
  SUM(IFF(s.OrderDate >= DATE_TRUNC('quarter', p.AsOf), s.Amount, 0)) AS QTD,
  SUM(IFF(s.OrderDate >= DATE_TRUNC('month', p.AsOf),   s.Amount, 0)) AS MTD
FROM Sales s
CROSS JOIN Params p
WHERE s.OrderDate >= DATE_TRUNC('year', p.AsOf)
  AND s.OrderDate <  DATEADD(day, 1, p.AsOf);

DATE_TRUNC returns the first day of the period that contains the reference date: 1 January, 1 April and 1 May. The WHERE clause keeps the rows from 1 January up to the reference date, and each IFF then counts only the rows inside its own period. A quarter and a month always start inside their year, so one scan is enough.

The breakdown shows which orders each total includes:

The order of 30 December belongs to the previous year and the one of 20 May comes after the reference date, so neither is counted.

Why the range ends before the next day

OrderDate < DATEADD(day, 1, AsOf) means “before the start of the following day”. On a DATE column it returns the same rows as <= AsOf. On a TIMESTAMP column it still includes the whole reference day, while <= '2026-05-15' stops at midnight and silently drops that day’s orders.

The upper bound also matters when the table holds rows dated after the reference date: scheduled orders, forecasts, or simply everything loaded since, when you rerun a report for a past date.

Choosing the reference date

CURRENT_DATE() suits tables that are updated during the day. If the table is loaded overnight, today has no rows yet, so use the last complete day instead:

SELECT DATEADD(day, -1, CURRENT_DATE()) AS AsOf;

On the first day of a month this reports the whole previous month as month to date, which is usually what the readers of a morning report expect.

CURRENT_DATE() returns the date in the session’s TIMEZONE. If the session runs in UTC and the business works in another time zone, the date changes at a different hour from the business day.

Weeks and fiscal years

Week to date works the same way, but DATE_TRUNC('week', ...) depends on the WEEK_START session parameter. How to calculate week to date shows a version that does not. Quarters and years here follow the calendar; for a fiscal year that starts in another month, see how to calculate fiscal year to date.

To put these totals next to last year’s, see how to compare YTD with the same period last year.