xmetricRecipes

Recipe Tableau and SQL

How do I make a running total that restarts every year?

Total each month, then add the months up in date order: RUNNING_SUM(SUM([revenue])) in Tableau, SUM(revenue) OVER (PARTITION BY year ORDER BY month ROWS UNBOUNDED PRECEDING) in SQL. The partition is where it restarts. At this kayak shop, 2026 beat 2025 in 8 of the first 9 months, yet the year-to-date gap through month 9 is −$2,047: one stormy July outweighed the rest.

Updated

The data

Tidewater Kayak Co. bookings, January 2025 to September 2026

Synthetic databookings.csv

A seasonal kayak rental on a tidal estuary. 2026 started stronger than 2025 every month, then a storm closed the water for two weeks of July.

booking_idbooking_datetripparty_sizerevenue
B-200012025-01-01Half-day estuary tour3256.5
B-200022025-01-03Sunset paddle tour3175.5
B-200032025-01-05Sunset paddle tour2130
B-200042025-01-05Sunset paddle tour2130

Step 1

Total each month

The file has one row per booking, often several on the same day. Totalling to one row per month first gives the running total one clean step per month. The shape is the season: quiet winters, a summer peak, and a dent in July 2026.

Tableau

SUM([revenue])
// Columns: MONTH([booking_date]) as a continuous month

SQL

SELECT
  CAST (date_trunc('month', booking_date) AS DATE) AS month,
  SUM(revenue) AS revenue
FROM input
GROUP BY 1
ORDER BY 1

Rows

MonthRevenue
2025-01-01$2,055
2025-02-01$1,826
2025-03-01$3,656
2025-04-01$5,761
2025-05-01$8,782
2025-06-01$14,033
2025-07-01$19,164
2025-08-01$16,806
2025-09-01$9,931
2025-10-01$5,461
2025-11-01$2,440
2025-12-01$2,081
2026-01-01$2,430
2026-02-01$2,024
2026-03-01$3,768
2026-04-01$5,896
2026-05-01$9,235
2026-06-01$16,540
2026-07-01$10,076
2026-08-01$17,799
2026-09-01$12,198

Step 2

Add the months up, never restarting

Each point is everything booked so far. The line is steep in summer and flat in winter. Useful for lifetime totals, but it can't tell you whether this year is ahead, because 2026 starts on top of all of 2025.

Tableau

RUNNING_SUM(SUM([revenue]))
// Compute Using: Table (across)

SQL

WITH m AS (
  SELECT
    CAST (date_trunc('month', booking_date) AS DATE) AS month,
    SUM(revenue) AS revenue
  FROM input
  GROUP BY 1
)
SELECT
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS running_total
FROM m
ORDER BY month

Rows

MonthRevenueRunning total
2025-01-01$2,055$2,055
2025-02-01$1,826$3,880
2025-03-01$3,656$7,537
2025-04-01$5,761$13,298
2025-05-01$8,782$22,079
2025-06-01$14,033$36,112
2025-07-01$19,164$55,277
2025-08-01$16,806$72,083
2025-09-01$9,931$82,014
2025-10-01$5,461$87,475
2025-11-01$2,440$89,915
2025-12-01$2,081$91,996
2026-01-01$2,430$94,426
2026-02-01$2,024$96,450
2026-03-01$3,768$100,218
2026-04-01$5,896$106,115
2026-05-01$9,235$115,350
2026-06-01$16,540$131,890
2026-07-01$10,076$141,966
2026-08-01$17,799$159,765
2026-09-01$12,198$171,963

Step 3

Restart the total every {knob.restart}

The formula didn't change. Only the partition did: the sum now drops back to zero at the start of each {knob.restart}. In SQL that is PARTITION BY; in Tableau it is the restart-every setting in Edit Table Calculation. Restart every year and you have year to date.

Tableau

RUNNING_SUM(SUM([revenue]))
// Compute Using: Specific Dimensions, tick Month of booking_date; restart every: {knob.restart} of booking_date

SQL

WITH m AS (
  SELECT
    CAST (date_trunc('month', booking_date) AS DATE) AS month,
    SUM(revenue) AS revenue
  FROM input
  GROUP BY 1
)
SELECT
  month,
  revenue,
  SUM(revenue) OVER (PARTITION BY date_trunc('{knob.restart}', month) ORDER BY month ROWS UNBOUNDED PRECEDING) AS running_total
FROM m
ORDER BY month

Rows

MonthRevenueRunning total
2025-01-01$2,055$2,055
2025-02-01$1,826$3,880
2025-03-01$3,656$7,537
2025-04-01$5,761$13,298
2025-05-01$8,782$22,079
2025-06-01$14,033$36,112
2025-07-01$19,164$55,277
2025-08-01$16,806$72,083
2025-09-01$9,931$82,014
2025-10-01$5,461$87,475
2025-11-01$2,440$89,915
2025-12-01$2,081$91,996
2026-01-01$2,430$2,430
2026-02-01$2,024$4,454
2026-03-01$3,768$8,222
2026-04-01$5,896$14,119
2026-05-01$9,235$23,354
2026-06-01$16,540$39,894
2026-07-01$10,076$49,970
2026-08-01$17,799$67,769
2026-09-01$12,198$79,967
Restart every

Where does a running total restart?

Wherever you partition it. The sum is the same in every step above; what changes is the dimension it starts over on. In Tableau's Edit Table Calculation dialog, tick the month (the direction the sum moves) and pick the year as the restart point. In SQL the same split is ORDER BY (the direction) and PARTITION BY (the restart).

Step 4

Lay this year over last year

With month number on the axis and year on colour, both years start at zero in January. The 2026 line runs just above 2025 all spring, then July flattens it and it never catches up.

Tableau

RUNNING_SUM(SUM([revenue]))
// Columns: MONTH([booking_date]) discrete; Color: YEAR([booking_date]); Compute Using: Month of booking_date

SQL

WITH m AS (
  SELECT
    CAST (date_trunc('month', booking_date) AS DATE) AS month,
    SUM(revenue) AS revenue
  FROM input
  GROUP BY 1
)
SELECT
  CAST (year(month) AS VARCHAR) AS year,
  month(month) : : INTEGER AS month_num,
  SUM(revenue) OVER (PARTITION BY year(month) ORDER BY month ROWS UNBOUNDED PRECEDING) AS year_to_date
FROM m
ORDER BY year, month_num

Rows

YearMonth numYear to date
20251$2,055
20252$3,880
20253$7,537
20254$13,298
20255$22,079
20256$36,112
20257$55,277
20258$72,083
20259$82,014
202510$87,475
202511$89,915
202512$91,996
20261$2,430
20262$4,454
20263$8,222
20264$14,119
20265$23,354
20266$39,894
20267$49,970
20268$67,769
20269$79,967

Step 5

Ahead or behind, month by month

The gap is 2026 year to date minus 2025 year to date. Six small wins built a lead; one bad month in peak season wiped it out, and two more good months didn't win it back. Monthly bars said "mostly better". The running total says "behind".

Tableau

RUNNING_SUM(SUM(IF YEAR([booking_date]) = 2026 THEN [revenue] END)) - RUNNING_SUM(SUM(IF YEAR([booking_date]) = 2025 THEN [revenue] END))
// Columns: MONTH([booking_date]) discrete; Compute Using: Month of booking_date; filter MONTH <= {knob.through}

SQL

WITH m AS (
  SELECT
    CAST (date_trunc('month', booking_date) AS DATE) AS month,
    SUM(revenue) AS revenue
  FROM input
  GROUP BY 1
), y AS (
  SELECT
    year(month) AS year,
    month(month) AS month_num,
    SUM(revenue) OVER (PARTITION BY year(month) ORDER BY month ROWS UNBOUNDED PRECEDING) AS ytd
  FROM m
)
SELECT
  month_num : : INTEGER AS month_num,
  MAX(ytd) FILTER (WHERE year = 2026)
    - MAX(ytd) FILTER (WHERE year = 2025) AS gap
FROM y
WHERE month_num <= 9
GROUP BY month_num
ORDER BY month_num

Rows

Month numGap
1$376
2$574
3$686
4$821
5$1,274
6$3,782
7−$5,307
8−$4,314
9−$2,047

Better in 8 months out of 9, and still behind?

Yes. The monthly bars show 2026 ahead in 8 of the first 9 months, and through June it led by $3,782. Then the storm: July came in −47.4% on the year before, in the month that carries the most bookings. A month's weight in the year is its share of the total, and peak months weigh the most. Slide the month to see the gap open and close.

19
Point at the line to read it.
1undefined$3,7822undefined$1,2743undefined$8214undefined$6865undefined$5746undefined$3767undefined−$2,0478undefined−$4,3149undefined−$5,307

Common mistake

A date filter restarts the count

Filters run before the running total, so filtering to July onwards throws away January to June and the year-to-date starts again from zero. Compute the running total first, then hide the months you don't want.

WrongStarts with 2026-07-01, 2026-08-01, 2026-09-01…
Point at a bar to read it.
020K40K2026-07-012026-08-012026-09-01

Tableau

RUNNING_SUM(SUM([revenue]))
// with a regular date filter: booking_date >= #2026-07-01#

SQL

WITH m AS (
  SELECT
    CAST (date_trunc('month', booking_date) AS DATE) AS month,
    SUM(revenue) AS revenue
  FROM input
  WHERE booking_date >= DATE '2026-07-01'
  GROUP BY 1
)
SELECT
  month,
  SUM(revenue) OVER (PARTITION BY year(month) ORDER BY month ROWS UNBOUNDED PRECEDING) AS year_to_date
FROM m
ORDER BY month
RightStarts with 2026-07-01, 2026-08-01, 2026-09-01…
Point at a bar to read it.
020K40K60K2026-07-012026-08-012026-09-01

Tableau

RUNNING_SUM(SUM([revenue]))
// remove the date filter; add a filter on LOOKUP(MIN([booking_date]), 0) >= #2026-07-01#, which runs after the table calculation

SQL

WITH m AS (
  SELECT
    CAST (date_trunc('month', booking_date) AS DATE) AS month,
    SUM(revenue) AS revenue
  FROM input
  GROUP BY 1
), r AS (
  SELECT
    month,
    SUM(revenue) OVER (PARTITION BY year(month) ORDER BY month ROWS UNBOUNDED PRECEDING) AS year_to_date
  FROM m
)
SELECT month, year_to_date
FROM r
WHERE month >= DATE '2026-07-01'
ORDER BY month

Common mistake

Ties: three bookings on one day share one total

SUM() OVER (ORDER BY booking_date) with no frame means RANGE, so every row on the same date gets the end-of-day total and the line jumps in steps you didn't ask for. Total to one row per period first, or spell out ROWS UNBOUNDED PRECEDING.

WrongStarts with 2025-03-02, 2025-03-02, 2025-03-02…
Point at a bar to read it.
02,0004,0002025-03-022025-03-022025-03-022025-03-01

SQL

SELECT
  booking_date,
  revenue,
  SUM(revenue) OVER (ORDER BY booking_date) AS running_total
FROM input
WHERE booking_date <= DATE '2025-03-02'
ORDER BY booking_date DESC
LIMIT 4
RightStarts with 2025-03-02, 2025-03-01, 2025-02-28…
Point at a bar to read it.
02,0004,0002025-03-022025-03-012025-02-282025-02-27

SQL

WITH d AS (
  SELECT booking_date, SUM(revenue) AS revenue
  FROM input
  GROUP BY 1
)
SELECT
  booking_date,
  revenue,
  SUM(revenue) OVER (ORDER BY booking_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM d
WHERE booking_date <= DATE '2025-03-02'
ORDER BY booking_date DESC
LIMIT 4

Run it on your own data

Any table with a date and a measure works: sales, sign-ups, donations, hours logged. Drop your CSV and pick the two columns; the steps recompute in your browser, and nothing is uploaded.

Drop a CSV anywhere on this page

Up to 10,000 rows. The sample, bookings.csv, shows the shape.

Questions

What is the difference between a running total and a cumulative sum?

None. Running total, cumulative sum and cumulative total are three names for the same thing: each row shows the sum of everything up to and including it.

My fiscal year doesn't start in January. What changes?

Restart on the fiscal year instead of the calendar year. For a year that starts in April and is named after the year it ends in, restart on YEAR(DATEADD('month', 9, [booking_date])) in Tableau, or PARTITION BY year(month + INTERVAL 9 MONTH) in SQL.

Why does my running total start from zero when I filter dates?

Filters run before the running total, so the months you filtered out never get added. Compute the running total first and hide months afterwards: a table-calculation filter in Tableau, an outer WHERE in SQL. The first trap below shows both.

Can a running total go inside a FIXED expression?

No. RUNNING_SUM is a table calculation, and table calculations can't be nested inside FIXED, INCLUDE or EXCLUDE. If you need the running balance on every row, compute it in SQL.

Sources