Python Excel AutomationPractical guides for developers
How-to
Advanced Data Transformation And CleaningDeep dive

Calculate Month-to-Date and Year-to-Date Totals with pandas

Compute MTD, QTD and YTD figures as of a report date, running period-to-date columns that reset each month or year, the same-period-last-year comparison that cuts last year at the same day, fiscal year-to-date with an April start, and a tidy summary table for an Excel report — including the leap-day and incomplete-data traps.
Dates & timesPivots & summaries

"How are we doing this month?" is a month-to-date question: sales from the first of the month up to today. Management packs add quarter-to-date and year-to-date, and every one of those figures is compared with the same period last year — which must stop at the same day last year, not at the end of last year's month, or this month always looks bad. In Excel these are SUMIFS formulas with date bounds that someone has to keep correct. In pandas they are boolean masks on a date column, plus groupby(...).cumsum() when the report needs a running column. This page builds both, and the comparison that people most often get wrong. It belongs to Working with Dates and Times in Excel Data.

Prerequisites

Bash
pip install pandas openpyxl

daily_sales_2y.xlsx has daily sales from 1 January 2025 to 2 October 2026, with no rows for Sundays — a realistic gap that matters for the comparisons below. The examples report as of 2 October 2026.

Step 1 — MTD, QTD and YTD as of a date

Python
import pandas as pd

sales = pd.read_excel("daily_sales_2y.xlsx", parse_dates=["Date"])
as_of = pd.Timestamp("2026-10-02")

def period_to_date(df, as_of, freq):
    start = as_of.to_period(freq).start_time
    mask = (df["Date"] >= start) & (df["Date"] <= as_of)
    return df.loc[mask, "Sales"].sum(), start

for label, freq in (("MTD", "M"), ("QTD", "Q"), ("YTD", "Y")):
    total, start = period_to_date(sales, as_of, freq)
    print(f"{label}: {total:>12,.0f}  ({start:%d %b %Y} – {as_of:%d %b %Y})")

as_of.to_period("M").start_time is the first day of the period containing the report date — 1 October for the month, 1 October for the quarter (Q4 starts in October), 1 January for the year. One function covers all three, and the printed date range makes the figure self-explanatory in a log or report. Pass the report date in explicitly rather than calling Timestamp.today() inside the function: reruns and backfills then produce the same numbers, as discussed in Backfill Excel Reports for Past Dates.

The three windows ending on the report date For a report date of 2 October 2026, year-to-date runs from 1 January 2026, quarter-to-date from 1 October 2026 and month-to-date from 1 October 2026. Each window ends on 2 October. 1 Jan 2026 YTD starts 1 Apr 2026 fiscal YTD starts (April year) 1 Oct 2026 QTD and MTD start 2 Oct 2026 report date — all windows end

Step 2 — The same period last year, to the same day

Python
def same_period_last_year(df, as_of, freq):
    ly_end = as_of - pd.DateOffset(years=1)
    ly_start = ly_end.to_period(freq).start_time
    mask = (df["Date"] >= ly_start) & (df["Date"] <= ly_end)
    return df.loc[mask, "Sales"].sum(), ly_start, ly_end

rows = []
for label, freq in (("MTD", "M"), ("QTD", "Q"), ("YTD", "Y")):
    this, _ = period_to_date(sales, as_of, freq)
    last, s, e = same_period_last_year(sales, as_of, freq)
    rows.append({"Measure": label, "This year": this, "Last year": last,
                 "Change %": (this / last - 1) * 100, "LY window": f"{s:%d %b} – {e:%d %b %Y}"})
summary = pd.DataFrame(rows)
print(summary.round(1).to_string(index=False))

Last year's window ends on 2 October 2025, not 31 October: comparing two days of this October with all of last October would show a 90-plus percent drop every month on day two. pd.DateOffset(years=1) moves the date back one calendar year and handles month lengths; for a report run on 29 February it lands on 28 February, which is the usual convention. The comparison is like-for-like in calendar days but not necessarily in trading days: 1–2 October 2026 are a Thursday and Friday, 1–2 October 2025 a Wednesday and Thursday, and with no Sunday trading a window that contains an extra Sunday has one fewer trading day. Step 5 deals with that.

Last year's month-to-date — two ways to get it wrong Comparing 1–2 October 2026 with all of October 2025 makes this month look 90 percent down. Comparing with 1–2 October 2025, the same days last year, gives a like-for-like change. Whole of last October 2 days vs 31 days always looks like a collapse early in the month improves by itself as the month goes on To the same day last year 1–2 Oct 2026 vs 1–2 Oct 2025 like-for-like calendar days trading days can still differ

Step 3 — Running period-to-date columns

Daily reports often want a column that accumulates through the month and resets on the first — the running MTD next to each day's sales:

Python
recent = sales[sales["Date"] >= "2026-09-01"].copy()
recent["MTD"] = recent.groupby(recent["Date"].dt.to_period("M"))["Sales"].cumsum()
recent["YTD"] = sales.groupby(sales["Date"].dt.year)["Sales"].cumsum().loc[recent.index]
print(recent.tail(5).to_string(index=False))
print(recent.loc[recent["Date"].isin(pd.to_datetime(["2026-09-30", "2026-10-01"])), ["Date", "Sales", "MTD"]].to_string(index=False))

Grouping by the month period and taking a cumulative sum gives a running total that restarts each month: 30 September carries the full September total, 1 October starts again from that day's sales. The YTD column is computed on the full table and then aligned to the recent rows by index, so it includes January to August. Running totals in general, including year-over-year growth, are in Running Totals and Year-Over-Year Growth in pandas.

Step 4 — Fiscal year-to-date

With a fiscal year starting in April, YTD starts on 1 April. Pandas periods can carry the fiscal year directly through an anchored frequency:

Python
fiscal_year = sales["Date"].dt.to_period("Q-MAR").dt.qyear      # FY ends in March
sales["FiscalYear"] = "FY" + fiscal_year.astype(str).str[-2:]
fy_label = sales.loc[sales["Date"] == as_of, "FiscalYear"].iloc[0]
fy_start = pd.Timestamp(year=as_of.year if as_of.month >= 4 else as_of.year - 1, month=4, day=1)
fytd = sales.loc[(sales["Date"] >= fy_start) & (sales["Date"] <= as_of), "Sales"].sum()
print(f"{fy_label} to date ({fy_start:%d %b %Y} – {as_of:%d %b %Y}): {fytd:,.0f}")

to_period("Q-MAR") creates quarters for a year ending in March, and .qyear is the fiscal year each date belongs to — 2 October 2026 is in fiscal year 2027, labelled FY27 here. The same labels come out of the date table in Build a Date Table for Excel Reports with pandas, which is the better home for fiscal logic when several reports share it.

Step 5 — Compare per trading day when calendars differ

When the number of trading days differs between the two windows, compare averages per trading day alongside the totals:

Python
def window(df, start, end):
    w = df[(df["Date"] >= start) & (df["Date"] <= end)]
    return w["Sales"].sum(), w["Date"].nunique()

this_total, this_days = window(sales, pd.Timestamp("2026-10-01"), as_of)
last_total, last_days = window(sales, pd.Timestamp("2025-10-01"), as_of - pd.DateOffset(years=1))
print(f"this year: {this_total:,.0f} over {this_days} trading days = {this_total / this_days:,.0f}/day")
print(f"last year: {last_total:,.0f} over {last_days} trading days = {last_total / last_days:,.0f}/day")

Counting distinct dates with sales gives the trading days in each window. In this sample both windows have two trading days, so the totals and the per-day averages tell the same story — an 11% drop on a two-day base, which says more about two individual days than about the month. On a window that straddles a Sunday in one year and not in the other, the two measures diverge. The totals answer "how much", the per-day averages answer "how well", and showing both prevents an extra trading day from passing as growth. Early in a month, when windows are a few days long, the per-day figure is the more honest of the two.

Step 6 — Write the summary sheet

Python
summary["This year"] = summary["This year"].round(0)
summary["Last year"] = summary["Last year"].round(0)
summary["Change %"] = (summary["Change %"] / 100).round(4)
with pd.ExcelWriter("period_to_date.xlsx", engine="xlsxwriter") as xw:
    summary.to_excel(xw, sheet_name="Summary", index=False, startrow=2)
    book, ws = xw.book, xw.sheets["Summary"]
    ws.write(0, 0, f"Period-to-date sales as of {as_of:%d %B %Y}", book.add_format({"bold": True, "font_size": 13}))
    ws.set_column("B:C", 14, book.add_format({"num_format": "#,##0"}))
    ws.set_column("D:D", 10, book.add_format({"num_format": "+0.0%;-0.0%"}))
    ws.set_column("E:E", 26)
print(summary.to_string(index=False))

The title states the report date, the windows are spelled out in the last column, and the change is formatted with an explicit sign. A reader can verify every number against the source without asking how it was computed — the best defence against "these don't match my spreadsheet".

The period-to-date summary sheet A summary sheet titled with the report date, listing MTD, QTD and YTD with this year's total, last year's total for the same window, the percentage change and the last-year window used. A B C D E 1 Measure This year Last year Change LY window 2 MTD 2,231 2,510 -11.1% 01 Oct – 02 Oct 2025 3 QTD 2,231 2,510 -11.1% 01 Oct – 02 Oct 2025 4 YTD 280,862 287,787 -2.4% 01 Jan – 02 Oct 2025 Early in a month a few days decide the MTD change; YTD moves slowly.

Performance notes

Each period-to-date figure is a boolean mask and a sum over the date column, which pandas evaluates in milliseconds even on millions of rows; computing a dozen measures by masking repeatedly is fine. If the table is very large and the report needs many windows, sort by date once and use searchsorted to find window boundaries, or aggregate to daily totals first — summing 600 daily rows is faster than masking 6 million transaction rows each time, and gives the same answer.

Common pitfalls

  • Comparing with last year's whole period. End last year's window on the same day.
  • Using today's date inside the calculation. Pass the report date in, so reruns are reproducible.
  • Incomplete data for the report date. If today's sales arrive overnight, report up to yesterday.
  • Leap days. DateOffset(years=1) maps 29 February to 28 February; be consistent about it.
  • Trading-day differences. Show per-trading-day averages next to totals.

Conclusion

Period-to-date figures are sums over a window from the start of the period to the report date: compute the start with to_period(freq).start_time, mask, and sum. Compare with last year by moving the report date back with DateOffset(years=1) and using the same-length window, not last year's full period. Use groupby(period).cumsum() for running columns that reset, Q-MAR periods for an April fiscal year, per-trading-day averages when calendars differ, and state the report date and windows in the output.

Frequently asked questions

How do I calculate month-to-date sales in pandas? Find the first day of the month with as_of.to_period("M").start_time, select rows with dates between that day and the report date, and sum the sales column.

How do I compare MTD with the same period last year? Move the report date back one year with as_of - pd.DateOffset(years=1), take the start of that month, and sum the window up to that same day. Do not compare with the whole of last year's month.

How do I add a running MTD column that resets each month? Group by the month period of the date column and take a cumulative sum of the sales within each group; the total restarts on the first of every month.

How do I calculate fiscal year-to-date for a year starting in April? Use 1 April of the current fiscal year as the window start — the same calendar year if the report month is April or later, otherwise the previous year. to_period("Q-MAR").dt.qyear gives the fiscal year number.

More on dates & times

More on dates & times →