A period comparison works a measure out again over another calendar period, such as last month or the same month last year. The Compare with menu writes it. In a query, it is a custom calculation. When Kater can prove that the numbers stay the same, it can become a reusable measure.

How it works

A period comparison does not read another result row. For each row, it calculates the measure again with the date moved back. This month’s row reads Orders over last month’s dates.

• It reads periods the result does not show. The comparison reads the source rows, so last month does not need a row in the result. A date filter in WHERE moves back with it.
• A missing period never falls back to an earlier row. The comparison reads the calendar period before. When that period has no rows, the value is what the measure gives over no rows: null for a sum, such as Units, and 0 for a count, such as Orders. A row lag reads the row before, whatever month it is. See Missing periods.
• It needs a date at a calendar grain. Group the query by a date at a grain, or select a finite date range. The grain sets the period: a date by month compares months. The examples group by KATER__MONTH(shop.order_date), the order date by month.
• It needs one date axis. When the query groups by one date axis, the comparison uses it. With more than one, name the axis with by =>.
• The menu can add the date. On a query with no date, Compare with offers the comparisons of a monthly axis. Writing one adds the order date by month to the query first.

Syntax

1
KATER__PREVIOUS_PERIOD(measure, by => date_axis, coverage => 'matching_elapsed')
2
KATER__PERIOD_SHIFT(measure, by => date_axis, unit => 'month', offset => -3)
3
KATER__PERCENT_CHANGE(EVAL(measure), baseline)

The comparisons

The menu names a comparison by the axis’s grain. On a month axis, it offers Previous Month, Same Month Last Year and Advanced Calendar Shift. On a day axis, it offers Previous Day, Same Date Last Year, Same Weekday Last Week and Advanced Calendar Shift. With more than one date axis, it says Previous Period.

An advanced shift moves by a unit the axis’s grain can hold:

Three outputs

Each comparison has three outputs. In the New calculation dialog, Show under Settings selects one.

• Percent change divides by the absolute baseline. It is (current - baseline) / ABS(baseline), so a rise from a negative baseline is positive.
• A zero or missing baseline gives null. There is no percent change from nothing.
• Percent change is a fraction. percent(1) shows 0.099 as 9.9%. Never multiply it by 100.
• Each output is its own field. Declare each one with its own CUSTOM_CALCULATION and name. AS gives a label only, so an output with no declaration has no field name, and no other model can read it.

Coverage

Coverage says what to compare with when the current period is not complete.

The dialog asks it as If the month is not over, with the axis’s grain in place of month. A week axis has matching_elapsed only. Coverage changes a number only when a date filter cuts the current period short. Grouped by month with no date filter, each month compares with the whole month before. The sample shop’s orders end on 2026-09-30, the last day of a month. So every month of the shop is complete, and both coverages give the same numbers.

September 1 to 15 against August

How do the first half of September’s orders compare with August?

1
SELECT
2
  KATER__MONTH(shop.order_date) AS "Order Month",
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'previous_month_matching__count_distinct__order_id',
6
    KATER__PREVIOUS_PERIOD(shop.count_distinct__order_id, by => KATER__MONTH(shop.order_date), coverage => 'matching_elapsed')
7
  ) AS "Orders (Previous Month, Same Days)",
8
  CUSTOM_CALCULATION(
9
    'previous_month_full__count_distinct__order_id',
10
    KATER__PREVIOUS_PERIOD(shop.count_distinct__order_id, by => KATER__MONTH(shop.order_date), coverage => 'full_period')
11
  ) AS "Orders (Previous Month, Whole Month)"
12
FROM workspace.shop AS shop
13
WHERE
14
  shop.order_date BETWEEN '2026-09-01' AND '2026-09-15'
15
GROUP BY
16
  KATER__MONTH(shop.order_date)

The filter keeps September 1 to 15, with 59 orders. Same days so far compares with August 1 to 15, which had 68 orders. The whole month compares with all of August, 145 orders. Half a month against a whole month makes September look much worse than it is.

Examples

Orders compared with the previous month

How did each month’s orders change from the month before?

1
SELECT
2
  KATER__MONTH(shop.order_date) AS "Order Month",
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'previous_month__count_distinct__order_id',
6
    KATER__PREVIOUS_PERIOD(shop.count_distinct__order_id, by => KATER__MONTH(shop.order_date))
7
  ) AS "Orders (Previous Month)",
8
  CUSTOM_CALCULATION(
9
    'previous_month_change__count_distinct__order_id',
10
    EVAL(shop.count_distinct__order_id) - KATER__PREVIOUS_PERIOD(shop.count_distinct__order_id, by => KATER__MONTH(shop.order_date))
11
  ) AS "Orders (Change vs Previous Month)",
12
  CUSTOM_CALCULATION(
13
    'previous_month_percent_change__count_distinct__order_id',
14
    KATER__PERCENT_CHANGE(
15
      EVAL(shop.count_distinct__order_id),
16
      KATER__PREVIOUS_PERIOD(shop.count_distinct__order_id, by => KATER__MONTH(shop.order_date))
17
    )
18
  ) AS "Orders (% Change vs Previous Month)" /* @format percent(1) */
19
FROM workspace.shop AS shop
20
GROUP BY
21
  KATER__MONTH(shop.order_date)
22
ORDER BY
23
  1

The first 5 of 21 rows. February had 155 orders against January’s 141, a change of 14, and 14 / 141 = 9.9%. The shop has no orders before January 2025. Orders is a count, and a count of no rows is 0, so January’s comparison value is 0. Its percent change is null, because the baseline is 0.

The same month last year

How did each month of 2026 do against the same month of 2025?

1
SELECT
2
  KATER__MONTH(shop.order_date) AS "Order Month",
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'same_month_last_year__count_distinct__order_id',
6
    KATER__SAME_PERIOD_LAST_YEAR(shop.count_distinct__order_id, by => KATER__MONTH(shop.order_date))
7
  ) AS "Orders (Same Month Last Year)",
8
  CUSTOM_CALCULATION(
9
    'same_month_last_year_percent_change__count_distinct__order_id',
10
    KATER__PERCENT_CHANGE(
11
      EVAL(shop.count_distinct__order_id),
12
      KATER__SAME_PERIOD_LAST_YEAR(shop.count_distinct__order_id, by => KATER__MONTH(shop.order_date))
13
    )
14
  ) AS "Orders (% Change vs Same Month Last Year)" /* @format percent(1) */
15
FROM workspace.shop AS shop
16
WHERE
17
  shop.order_date BETWEEN '2026-01-01' AND '2026-09-30'
18
GROUP BY
19
  KATER__MONTH(shop.order_date)
20
ORDER BY
21
  1

The first 6 of 9 rows. The WHERE keeps only 2026, but the comparison still reads 2025: the date filter moves back a year with it. January 2026 had 145 orders against 141 in January 2025, so it rose 2.8%.

The same weekday last week

How did each day of the last full week of September 2026 compare with the same weekday a week before? This comparison needs a day axis, so the query groups by the order date itself.

1
SELECT
2
  shop.order_date,
3
  shop.day_name__order_date,
4
  EVAL(shop.count_distinct__order_id),
5
  CUSTOM_CALCULATION(
6
    'same_weekday_last_week__count_distinct__order_id',
7
    KATER__SAME_WEEKDAY_LAST_WEEK(shop.count_distinct__order_id, by => shop.order_date)
8
  ) AS "Orders (Same Weekday Last Week)"
9
FROM workspace.shop AS shop
10
WHERE
11
  shop.order_date BETWEEN '2026-09-21' AND '2026-09-27'
12
GROUP BY
13
  shop.order_date,
14
  shop.day_name__order_date
15
ORDER BY
16
  1

Each day reads the day seven days earlier. Monday, September 21, had 8 orders, and Monday, September 14, had 7.

Three months back

How do each month’s orders compare with the month three months before? Advanced Calendar Shift moves by any number of units you set.

1
SELECT
2
  KATER__MONTH(shop.order_date) AS "Order Month",
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'shift_3_months_back__count_distinct__order_id',
6
    KATER__PERIOD_SHIFT(
7
      shop.count_distinct__order_id,
8
      by => KATER__MONTH(shop.order_date),
9
      unit => 'month',
10
      offset => -3
11
    )
12
  ) AS "Orders (3 Months Back)"
13
FROM workspace.shop AS shop
14
WHERE
15
  shop.order_date BETWEEN '2026-04-01' AND '2026-09-30'
16
GROUP BY
17
  KATER__MONTH(shop.order_date)
18
ORDER BY
19
  1

offset => -3 is three months earlier. April 2026 reads January 2026, which had 145 orders. A positive offset compares with a later period.

Revenue compared with last month

Revenue cannot be added up across currencies. See Measures you cannot add up. So a comparison of Revenue must keep Currency in the grouping, as Revenue itself must.

1
SELECT
2
  KATER__MONTH(shop.order_date) AS "Order Month",
3
  shop.currency,
4
  EVAL(shop.sum__amount),
5
  CUSTOM_CALCULATION(
6
    'previous_month__sum__amount',
7
    KATER__PREVIOUS_PERIOD(shop.sum__amount, by => KATER__MONTH(shop.order_date))
8
  ) AS "Revenue (Previous Month)" /* @format currency(currency, 2) */
9
FROM workspace.shop AS shop
10
WHERE
11
  shop.order_date BETWEEN '2026-08-01' AND '2026-09-30'
12
GROUP BY
13
  KATER__MONTH(shop.order_date),
14
  shop.currency
15
ORDER BY
16
  1,
17
  2

Each currency compares with its own last month. September’s euro row reads August’s euros, €3,678.81. Without Currency in the GROUP BY, the compiler refuses the query with not_additive_across: “Revenue cannot be added up across Currency. Group by Currency or filter it to one value.”

The previous month in Monthly Revenue

The sample shop’s monthly_revenue query declares last month’s Revenue beside each month’s. It groups by month and currency, and it writes the coverage out:

1
  CUSTOM_CALCULATION(
2
    'previous_month_value__sum__amount',
3
    KATER__PREVIOUS_PERIOD(
4
      shop.sum__amount,
5
      by => KATER__MONTH(shop.order_month),
6
      coverage => 'matching_elapsed'
7
    )
8
  ) AS "Revenue (Previous Month)" /* @format currency(currency, 2) */

Six of its 42 rows, and four of its seven columns. Revenue is a sum, and a sum of no rows is null. So January 2025, with no month before it, shows null in both currencies. The same query also keeps a running total and a percent delta. Those are row windows, declared with CUSTOM_CALCULATION. See Running totals, ranks and windows.

Missing periods

A period comparison reads the calendar period before. A row lag reads the row before. They differ when a period has no rows. This query keeps web orders with no region, which skip some months:

1
SELECT
2
  KATER__MONTH(shop.order_date) AS "Order Month",
3
  EVAL(shop.sum__quantity),
4
  CUSTOM_CALCULATION(
5
    'previous_month__sum__quantity',
6
    KATER__PREVIOUS_PERIOD(shop.sum__quantity, by => KATER__MONTH(shop.order_date))
7
  ) AS "Units (Previous Month)",
8
  CUSTOM_CALCULATION(
9
    'lag__sum__quantity',
10
    KATER__LAG(EVAL(shop.sum__quantity)) OVER (ORDER BY KATER__MONTH(shop.order_date) ASC)
11
  ) AS "Units (Lag)"
12
FROM workspace.shop AS shop
13
WHERE
14
  shop.channel = 'web'
15
  AND shop.region IS NULL
16
GROUP BY
17
  KATER__MONTH(shop.order_date)
18
ORDER BY
19
  1

The first 6 of 12 rows. March 2025 has no such orders, so April’s previous month is null. The lag reads the row before, January, and shows its 9 units as if they were March’s. When the months follow each other, as in July to September, the two agree. Units is a sum, so a month with no rows is null. For a count, such as Orders, it is 0.

Convert a comparison to a measure

A comparison in a query is a custom calculation, but it is not a window. When the comparison is complete and reads a reusable measure, its menu offers Edit › Convert to a measure. The conversion keeps the name, the label, the axis, the grain and the shift. It changes only the wrapper, from CUSTOM_CALCULATION to MEASURE. A reader then works the comparison out again at its own grouping, from the current and the earlier totals.

• It is offered only after a proof. Kater offers it only when it can prove that the measure gives the same numbers. A WHERE, a join, a HAVING or a QUALIFY in the query blocks it.
• All three outputs convert. A percent change works out both totals again before it divides. It never averages the percentages a result shows.
• A row lag never converts. Lag, Delta and Percent Delta read the row before, not the period before, so no measure gives their numbers. Changing their wrapper does not make them calendar comparisons.

A previous month a reader can regroup

This query counts orders by month and channel, with last month’s orders as a measure:

1
SELECT
2
  KATER__MONTH(shop.order_date) AS "Order Month",
3
  shop.channel,
4
  EVAL(shop.count_distinct__order_id),
5
  MEASURE(
6
    'previous_month__count_distinct__order_id',
7
    KATER__PREVIOUS_PERIOD(shop.count_distinct__order_id, by => KATER__MONTH(shop.order_date))
8
  ) AS "Orders (Previous Month)"
9
FROM workspace.shop AS shop
10
GROUP BY
11
  KATER__MONTH(shop.order_date),
12
  shop.channel
13
ORDER BY
14
  1,
15
  2

A query that reads orders_by_channel groups by month only:

1
SELECT
2
  q.order_date,
3
  q.previous_month__count_distinct__order_id
4
FROM query.orders_by_channel AS q
5
GROUP BY
6
  q.order_date
7
ORDER BY
8
  1

The first 4 of 21 rows. The reader works the measure out again for each month across all channels: February’s previous month is January’s 141 orders. As a custom calculation, it would be one finished value for each month and channel, not a total the reader can work out again. A reader must still group by the date axis, or the compiler refuses it.

Filter a comparison

Filter by this number on a comparison writes the condition in HAVING. The comparison is a value the query works out at its grouping, not a window. A comparison runs no window, so it also offers Is In The Top and Is In The Bottom, which go in QUALIFY.

Months that grew more than 15%

1
SELECT
2
  KATER__MONTH(shop.order_date) AS "Order Month",
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'previous_month_percent_change__count_distinct__order_id',
6
    KATER__PERCENT_CHANGE(
7
      EVAL(shop.count_distinct__order_id),
8
      KATER__PREVIOUS_PERIOD(shop.count_distinct__order_id, by => KATER__MONTH(shop.order_date))
9
    )
10
  ) AS "Orders (% Change vs Previous Month)" /* @format percent(1) */
11
FROM workspace.shop AS shop
12
GROUP BY
13
  KATER__MONTH(shop.order_date)
14
HAVING
15
  KATER__IS_GREATER_THAN(
16
    KATER__PERCENT_CHANGE(
17
      EVAL(shop.count_distinct__order_id),
18
      KATER__PREVIOUS_PERIOD(shop.count_distinct__order_id, by => KATER__MONTH(shop.order_date))
19
    ),
20
    0.15
21
  )
22
ORDER BY
23
  1

The filter keeps four of the 21 months. Each one still compares with its month before, though that month is not in the result: August 2025’s 151 orders against July’s 130.

Common errors