Compare Two Periods Side by Side in a Pivot

"How did September do compared with last September?" is the most common question a sales report has to answer, and a pivot with two period columns, a difference and a percentage is the most common answer. The details are where these tables go wrong. Comparing a partial month with a full one makes every figure look like a collapse. A product that did not exist last year has no sensible percentage change, and a product that was discontinued shows a confusing −100%. A list of percentage changes hides what actually moved the total: a small product doubling matters less than a big one slipping 5%. This page builds the comparison properly with pandas — like-for-like periods, a side-by-side pivot, changes that handle new and lost lines, the contribution of each line to the total change — and writes it to Excel. It belongs to Creating Pivot Tables from Excel Data.

Prerequisites

pip install pandas openpyxl

daily_sales.xlsx holds daily sales by product from August 2025 to September 2026. Most products sold in both years; one (Lantern) was launched in March 2026 and one (Trowel) was discontinued at the end of 2025. The report compares September 2026 with September 2025.

Step 1 — Choose periods that are really comparable

A comparison is only fair if both periods cover the same kind of days. For a closed month, compare with the same month a year earlier; mid-month, compare month-to-date with the same days of the comparison month:

import pandas as pd

sales = pd.read_excel("daily_sales.xlsx")

def label_periods(df, current_start, current_end, offset):
    prior_start, prior_end = current_start - offset, current_end - offset
    out = df.copy()
    out["Period"] = None
    out.loc[out["Date"].between(current_start, current_end), "Period"] = "Current"
    out.loc[out["Date"].between(prior_start, prior_end), "Period"] = "Prior"
    return out.dropna(subset=["Period"]), (prior_start, prior_end)

cur_start, cur_end = pd.Timestamp("2026-09-01"), pd.Timestamp("2026-09-30")
labelled, prior = label_periods(sales, cur_start, cur_end, pd.DateOffset(years=1))
print("current:", cur_start.date(), "→", cur_end.date(), "| prior:", prior[0].date(), "→", prior[1].date())
print(labelled.groupby("Period")["Revenue"].sum().round(2).to_dict())

pd.DateOffset(years=1) shifts both ends of the window by exactly one year, so September 2026 is compared with September 2025. For a month-to-date report on 15 September, pass current_end=2026-09-15 and the prior window becomes 1–15 September 2025 — the same number of days. For weekly retail reporting, an offset of 52 weeks keeps weekdays aligned (a Saturday compared with a Saturday), which often matters more than the calendar date. The labels "Current" and "Prior" keep the rest of the code independent of which periods were chosen.

Step 2 — Pivot the two periods side by side

With every row labelled, one pivot puts the periods in columns:

pivot = labelled.pivot_table(index="Product", columns="Period", values="Revenue", aggfunc="sum")
pivot = pivot.reindex(columns=["Prior", "Current"])
print(pivot.round(2).to_string())

Each product has its September 2025 revenue under "Prior" and its September 2026 revenue under "Current". Notice the missing values: Lantern has no prior revenue because it did not exist, and Trowel has no current revenue because it was discontinued. pandas leaves these as NaN, which is right — they are not zeros in the same sense as a product that was on sale and sold nothing — and the next step treats them deliberately rather than letting them produce odd percentages.

Step 3 — Changes that cope with new and lost lines

Compute the absolute change with missing values treated as zero, but give new and lost products a status instead of a meaningless percentage:

cmp = pivot.copy()
cmp["Change"] = cmp["Current"].fillna(0) - cmp["Prior"].fillna(0)
cmp["Change %"] = (cmp["Change"] / cmp["Prior"] * 100).where(cmp["Prior"].notna() & cmp["Current"].notna())
cmp["Status"] = "continuing"
cmp.loc[cmp["Prior"].isna(), "Status"] = "new"
cmp.loc[cmp["Current"].isna(), "Status"] = "lost"
print(cmp.round(1).sort_values("Change", ascending=False).to_string())

For the absolute change, a missing value really does contribute zero revenue, so fillna(0) is correct: Lantern adds its whole current revenue, Trowel subtracts its whole prior revenue. The percentage change is computed only for products present in both periods; for the others it would be infinite or −100%, which says nothing useful and distorts any average. The status column says what happened instead. Sorting by absolute change puts the products that moved the total most at the top — which is usually the question being asked.

Step 4 — Contribution to the total change

The total moved by some amount; how much of that is due to each product? Each product's change divided by the total change is its contribution:

totals = cmp[["Prior", "Current", "Change"]].sum()
total_change_pct = totals["Change"] / totals["Prior"] * 100
cmp["Contribution pts"] = cmp["Change"] / totals["Prior"] * 100
print(f"total: {totals['Prior']:,.2f} → {totals['Current']:,.2f}  ({total_change_pct:+.1f}%)")
print(cmp["Contribution pts"].round(2).sort_values(ascending=False).to_string())
print("contributions add up to total change:", round(cmp["Contribution pts"].sum(), 6) == round(total_change_pct, 6))

Contribution in percentage points — each product's change divided by the prior total — has the neat property that the contributions add up exactly to the total percentage change, so the table explains the headline figure. It also puts percentage changes in proportion: Ladder's revenue grew by 48.8%, but from a small base, so it contributes 4.8 points — half of what the Drill's 15.6% growth on a large base contributes (9.4 points) — and the Saw's 20% fall shows up as the biggest drag at −1.6 points. Together the eight contributions add up to the total growth of 18.2%. This is the column managers actually read.

The same structure works for any pair of periods — this quarter against the last, this week against the same week last year, year to date against the prior year to date — because only the labelling step knows which dates belong to which period.

Step 5 — Write the comparison to Excel

The report sheet shows the periods, the change, the percentage, the status and the contribution, with arrows that make direction obvious at a glance:

from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import Font, PatternFill

report = cmp.sort_values("Contribution pts", ascending=False).reset_index()
report.columns = ["Product", "Sep 2025", "Sep 2026", "Change", "Change %", "Status", "Contribution (pts)"]
report.loc[len(report)] = ["Total", totals["Prior"], totals["Current"], totals["Change"], total_change_pct, "", total_change_pct]

with pd.ExcelWriter("period_comparison.xlsx", engine="openpyxl") as xw:
    report.to_excel(xw, sheet_name="Sep vs Sep", index=False)
    ws = xw.sheets["Sep vs Sep"]
    last = ws.max_row
    for row in ws.iter_rows(min_row=2, max_row=last, min_col=2, max_col=4):
        for c in row:
            c.number_format = "#,##0;[Red]-#,##0"
    for row in ws.iter_rows(min_row=2, max_row=last, min_col=5, max_col=7):
        for c in row:
            if c.column != 6:
                c.number_format = '[Color10]"▲ "0.0;[Red]"▼ "0.0;0.0'
    for c in ws[last]:
        c.font = Font(bold=True)
    ws.conditional_formatting.add(f"F2:F{last}", CellIsRule(operator="equal", formula=['"new"'], fill=PatternFill("solid", fgColor="DDEBF7")))
    ws.conditional_formatting.add(f"F2:F{last}", CellIsRule(operator="equal", formula=['"lost"'], fill=PatternFill("solid", fgColor="F2F2F2")))
    for col, w in zip("ABCDEFG", (12, 12, 12, 12, 11, 12, 18)):
        ws.column_dimensions[col].width = w
print(report.round(1).to_string(index=False))

The custom number format [Color10]"▲ "0.0;[Red]"▼ "0.0;0.0 shows positive percentages and contributions with a green up-arrow and negative ones with a red down-arrow, while the cell still holds a plain number that can be sorted and summed. New and lost products are shaded so their blank percentage cells are explained. The total row at the bottom carries the headline change, which the contribution column adds up to. Arrow formats are covered in more detail in Add Variance Arrows and Trend Indicators to a Dashboard.

Common pitfalls

• Partial periods compared with full ones. Compare month-to-date with the same days of the prior period.
• Percent change for new or lost lines. Show a status instead of infinite or −100%.
• Filling the pivot with zeros before computing percentages. Absolute change uses zeros; percentages need both values.
• Ranking by percentage change. Rank by absolute change or contribution; percentages exaggerate small lines.
• Contributions that do not add up. Divide each change by the prior total, not by each line's own prior value.

Conclusion

A fair period comparison starts with like-for-like windows — the same month a year earlier, or the same days for month-to-date — labelled Current and Prior and pivoted side by side. Compute absolute change with missing values as zero, percentage change only where both periods have values, and a status for new and lost lines. Add each line's contribution in percentage points so the table explains the headline change, rank by it, and write the result with arrow number formats that keep the cells numeric.

Frequently asked questions

How do I compare this year with last year in a pandas pivot? Label each row Current or Prior by date window, then pivot_table with the product as index and the period label as columns, and compute the difference and percentage change between the two columns.

How should new products appear in a year-over-year comparison? Their prior value is missing, so the absolute change equals the current value, but the percentage change should be left blank and the product marked as new.

How do I calculate each product's contribution to total growth? Divide each product's change by the total prior value and multiply by 100. These percentage-point contributions add up to the total percentage change.

How do I compare month-to-date figures fairly? Compare the current month to date with the same day range of the comparison month or year, rather than with the full prior month.

Related

• Up one level: Creating Pivot Tables from Excel Data — summaries and crosstabs.
• Running Totals and Year-over-Year Growth in pandas — growth over a time series.
• Calculate Month-to-Date and Year-to-Date Totals with pandas — the period windows themselves.
• Build a Budget vs Actual Variance Report — comparing against a plan instead of a period.