Level 7 · The partial-month trap

Period-over-period numbers are only fair when both periods are complete and comparable. A dashboard refreshed in the middle of the month breaks the first rule every single day.

What the dashboard did

The query behind the tile was a plain monthly sum, then a growth calculation:

-- monthly revenue, then (this month - last month) / last month
SELECT CAST(DATE_TRUNC('month', o.order_date) AS DATE) AS month,
       SUM(i.quantity * i.unit_price * (1 - i.discount_pct / 100.0)) AS net_revenue
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'completed' AND i.quantity > 0
GROUP BY 1;

On December 15 the December row holds 14 days, the November row holds 30. The -43.9% is the calendar talking, not the business.

Month-to-date, done right

Compare the same days of the same month a year earlier:

SELECT
  SUM(r) FILTER (WHERE d >= '2025-12-01' AND d < '2025-12-15') AS mtd_2025,
  SUM(r) FILTER (WHERE d >= '2024-12-01' AND d < '2024-12-15') AS mtd_2024
FROM (
  SELECT o.order_date AS d,
         i.quantity * i.unit_price * (1 - i.discount_pct / 100.0) AS r
  FROM orders AS o
  JOIN order_items AS i ON i.order_id = o.order_id
  WHERE o.status = 'completed' AND i.quantity > 0
) AS lines;

That returns $164,124.86 and $151,464.25: December is up 8.4% so far. The windows use >= start AND < end so a timestamp late on the 14th is included and nothing from the 15th sneaks in.

Why not the first half of November?

November 1–14, 2025 is the same number of days ($112,031.17), but it sits before Black Friday, while early December is peak gift season. The comparison says +46.5%, which is mostly the season again. Year-over-year on the same days removes the seasonal pattern because last year had the same calendar.

One more partial-period trap

Completed revenue for the latest days is also incomplete in another way: almost all of Harbor & Pine’s pending orders sit in the last days of 2025, and they are not “completed” yet. Recent periods filtered on status = 'completed' keep growing for a while after they end. When you report the latest period, say so.

Common mistakes

• Comparing a partial period with a full one. Every mid-month refresh shows a “drop”.
• Fixing the day count but ignoring the season. Early November is not comparable with early December.
• Using daily averages on an unfinished month without dividing by the days elapsed.

In SQL Server

Month-to-date bounds come from DATEFROMPARTS(YEAR(@d), MONTH(@d), 1) and the same days last year from DATEADD(year, -1, ...). There is no FILTER, so use SUM(CASE WHEN ... THEN r END). EOMONTH(@d) gives the last day of a month when you need a full-month bound.