How to compare an incomplete month with the same days of last month in Snowflake

On 15 May, May’s sales cover half a month. Compared with the whole of April they always look like a drop. The fair benchmark is the same days of April: 1 to 15 May against 1 to 15 April.

A small example

CREATE TEMP TABLE Sales (OrderDate DATE, Amount NUMBER(10,2));
INSERT INTO Sales VALUES
  ('2026-04-03', 60),  ('2026-04-08', 80),  ('2026-04-15', 40),
  ('2026-04-16', 90),  ('2026-04-28', 200),
  ('2026-05-02', 120), ('2026-05-15', 50),  ('2026-05-20', 300);

The query

The structure is the same as in how to compare YTD with the same period last year, with months in place of years.

WITH Params AS (
  SELECT '2026-05-15'::DATE AS AsOf   -- CURRENT_DATE() in a live report
),
Periods AS (
  SELECT 'This month' AS Period,
         DATE_TRUNC('month', AsOf) AS StartDate,
         AsOf AS EndDate
  FROM Params
  UNION ALL
  SELECT 'Last month',
         DATE_TRUNC('month', DATEADD(month, -1, AsOf)),
         DATEADD(month, -1, AsOf)
  FROM Params
)
SELECT p.Period, p.StartDate, p.EndDate,
       DATEDIFF(day, p.StartDate, p.EndDate) + 1 AS DayCount,
       COALESCE(SUM(s.Amount), 0) AS Sales
FROM Periods p
LEFT JOIN Sales s
  ON  s.OrderDate >= p.StartDate
  AND s.OrderDate <  DATEADD(day, 1, p.EndDate)
GROUP BY p.Period, p.StartDate, p.EndDate
ORDER BY p.StartDate DESC;

Over the same fifteen days, May is 5.6% behind April. Against the whole of April, 470, it would look 63.8% behind.

When the days do not line up

Months have 28 to 31 days, so near the end of a month the same day number may not exist in the previous month. DATEADD then moves to the last day of the shorter month:

From 28 March on, the benchmark is the whole of February, while March keeps growing to 29, 30 and 31 days. That cannot be avoided, and the DayCount column makes it visible.

The opposite case is a real error. On 30 April the month is complete, but DATEADD returns 30 March, so all of April is compared with March without its last day. At month end, compare with the end of the previous month instead. In the Periods CTE, replace the EndDate of last month, DATEADD(month, -1, AsOf), with:

IFF(AsOf = LAST_DAY(AsOf),
    LAST_DAY(DATEADD(month, -1, AsOf)),
    DATEADD(month, -1, AsOf))

That is the third column of the table above.

Sales per day

When the two periods have different lengths, compare the average per day rather than the total. Add this column to the final SELECT:

COALESCE(SUM(s.Amount), 0) / (DATEDIFF(day, p.StartDate, p.EndDate) + 1) AS SalesPerDay

On 31 March, 31 days of March against 28 days of February is then a fair comparison. The mix of weekdays still differs: a period with one more weekend can move the average on its own.

Early in the month

On 1 or 2 May the comparison covers one or two days, and a single large order changes the percentage completely. Many reports show the month-to-date comparison only from the second week, or show the amounts without a percentage until then.

If the table is loaded overnight, use yesterday as the reference date, as explained in how to calculate YTD, QTD and MTD. Otherwise an empty day this month is compared with a complete day last month.