Level 1 · Year-to-date that resets every January

A running total answers “how much so far?”. Finance rarely wants “so far, ever” though. They want “so far this year”, which starts at zero every January. That is one extra clause.

Two running totals, one difference

You met SUM(...) OVER (ORDER BY month) in the window functions chapter: with an ORDER BY inside OVER, each row sums every row up to and including itself. Add PARTITION BY and the sum restarts in every partition:

SUM(net_revenue) OVER (PARTITION BY YEAR(month) ORDER BY month) AS ytd_revenue,
SUM(net_revenue) OVER (ORDER BY month)                          AS cumulative_revenue

YEAR(month) is 2023, 2024 or 2025, so the 36 monthly rows fall into three windows of twelve. The partition does not have to be a column in the output. Any expression works.

Worked example: the turn of the year

With completed orders and lines with a positive quantity, the rows around January 2024 look like this:

month	net_revenue	ytd_revenue	cumulative_revenue
2023-12-01	241,527.32	1,600,531.40	1,600,531.40
2024-01-01	109,305.64	109,305.64	1,709,837.04
2024-02-01	77,546.58	186,852.21	1,787,383.61

December 2023’s year-to-date is the full 2023 total. January 2024 starts over, while the lifetime column carries on. By December 2025 the lifetime total is $5,896,296.81 and the 2025 year-to-date is $2,301,690.14.

Reading year-to-date

Year-to-date is most useful against the same point last year. At the end of June, 2025 stood at $1,026,835.51 against $789,588.04 for 2024, about 30% ahead. Be careful extrapolating a half year to a full year at Harbor & Pine: November and December alone brought in 25% of 2025’s revenue, so a store that is “half way” in June is not half way to its annual number.

Aggregate first, then window

Window functions run after GROUP BY. Sum the order lines to months in a CTE, then run the window over 36 rows. Writing SUM(SUM(...)) OVER (...) in one query also works, but the CTE is easier to read and to check.

Common mistakes

• Partitioning by the wrong thing. PARTITION BY month gives every row its own window, so the running total equals the monthly value.
• Forgetting ORDER BY inside OVER. SUM(net_revenue) OVER (PARTITION BY YEAR(month)) returns the full-year total on every row, not a running one.
• Gaps in months. A running total over a month with no sales simply skips that month. Here every month has sales; on sparse data, build a calendar first.

In SQL Server

There is no DATE_TRUNC before SQL Server 2022 (DATETRUNC(month, order_date) in 2022+). Use DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1). Also add an explicit frame: the default is RANGE, which is slower and treats tied dates as one step.

SUM(net_revenue) OVER (PARTITION BY YEAR(month) ORDER BY month
                       ROWS UNBOUNDED PRECEDING) AS ytd_revenue