Financial
Year-to-Date (YTD), Month-to-Date (MTD), and prior-year totals
Calculate cumulative YTD, MTD, and Same-Period-Last-Year (PYTD) totals anchored to a single As-Of reporting date.
Anchor YTD, MTD, and Prior-Year YTD to one As-Of cell
Financial dashboards compare cumulative Year-to-Date revenue from Jan 1 through an As-Of cutoff C2 against the exact same calendar window one year earlier—never against a full 12-month prior year.
YTD spans DATE(YEAR(C2), 1, 1) to C2; MTD spans DATE(YEAR(C2), MONTH(C2), 1) to C2; Prior-Year YTD shifts both endpoints back 12 months with DATE(YEAR(C2) - 1, 1, 1) and EDATE(C2, -12).
| TxDate (A) | Sales (B) | Window (AsOf = 2026-04-15) |
|---|---|---|
| 2025-02-10 | 800 | PYTD (2025-01-01..2025-04-15) |
| 2025-09-01 | 900 | After PYTD cutoff |
| 2026-01-20 | 600 | YTD only |
| 2026-04-10 | 500 | Both YTD and MTD |
| 2026-04-25 | 700 | Future (after AsOf) |
YTD, MTD, and Prior-Year YTD formulas
Changing C2 updates YTD, MTD, and YoY growth simultaneously across the report.
=SUMIFS(B2:B6, A2:A6, ">=" & DATE(YEAR(C2), 1, 1), A2:A6, "<" & (INT(C2)+1))Prior-Year YTD (apples-to-apples YoY window)
EDATE(C2, -12) shifts April 15, 2026 back to April 15, 2025 (and clips Feb 29 leap days safely to Feb 28).
=SUMIFS(B2:B6, A2:A6, ">=" & DATE(YEAR(C2) - 1, 1, 1), A2:A6, "<" & (EDATE(INT(C2), -12)+1))Inputs and interpretation
These formulas include the entire As-Of calendar day, even if C2 contains a time. They are calendar-year, not fiscal-year totals. Prior-year Feb 29 is clipped to Feb 28; this chosen calendar-date comparison can have different day counts across years. Use numeric timestamps with a consistent time zone and numeric amounts. YoY percentage, if added, needs an explicit policy for a zero prior total.
Try the example
Use the original values or change them to test your understanding.