How much history do I need?

Three complete years at least. The centered average loses six months at each end, so three years give three ratios per calendar month, which is the minimum for a median to mean anything. Two years work for a rough simple index.

What do I do with the index once I have it?

Divide a month's actual by its index to get a seasonally adjusted figure: a January of 50,000 rides with an index of 0.6 runs at about 83,000 a month. Multiply a flat trend forecast by each month's index to get a seasonal forecast.

Why do my twelve indices not average exactly 1?

The trend ratios come from different years for different months, so their average drifts a little. Divide each index by the mean of the twelve if you need them to average exactly 1.

Can I do day-of-week seasonality the same way?

Yes. Use daily totals, a centered 7-day average as the trend, and group the ratios by weekday.

Recipe Tableau and SQL

How do I measure seasonality when the business is growing?

Divide each month by a centered 12-month moving average, then average those ratios for each calendar month. Dividing by the year's average month instead lets growth leak in: in this bike-share, that simple index calls October 1.03 of a typical month, above average, when with growth removed it is 0.94.

Updated October 30, 2026

The data

Riverside Bikes, daily rides 2022 to 2025

Synthetic datarides.csv

A city bike-share that keeps adding stations: ridership grows about 3% a month, peaks in July, and had a free-ride week in September 2023.

`ride_date`	`rides`
2022-01-01	484
2022-01-02	542
2022-01-03	419
2022-01-04	455

Step 1

Total rides per month

Two things at once: a summer hump every year, and a line that keeps climbing. Each year has roughly half again the rides of the year before. A seasonality index has to separate the two.

Tableau

SUM([rides])
// MONTH([ride_date]) as a continuous month on Columns

SQL

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

Rows

Month	Rides
2022-01-01	14,665
2022-02-01	15,131
2022-03-01	23,265
2022-04-01	27,316
2022-05-01	35,859
2022-06-01	38,498
2022-07-01	41,062
2022-08-01	41,458
2022-09-01	34,184
2022-10-01	31,334
2022-11-01	26,384
2022-12-01	28,352
2023-01-01	22,622
2023-02-01	22,155
2023-03-01	32,690
2023-04-01	39,968
2023-05-01	50,508
2023-06-01	56,395
2023-07-01	61,251
2023-08-01	59,387
2023-09-01	75,443
2023-10-01	45,236
2023-11-01	39,160
2023-12-01	43,544
2024-01-01	32,186
2024-02-01	33,925
2024-03-01	47,315
2024-04-01	59,286
2024-05-01	75,204
2024-06-01	80,258
2024-07-01	90,506
2024-08-01	90,688
2024-09-01	72,679
2024-10-01	65,442
2024-11-01	57,238
2024-12-01	64,256
2025-01-01	48,211
2025-02-01	47,998
2025-03-01	70,769
2025-04-01	84,720
2025-05-01	107,383
2025-06-01	119,756
2025-07-01	130,013
2025-08-01	127,277
2025-09-01	101,679
2025-10-01	97,667
2025-11-01	81,533
2025-12-01	88,510

Step 2

The simple index: each month against its year

Each month divided by the average month of its own year, then averaged over the four years. It's the common recipe, and it's quick. But inside each year the business grew, so January is compared with a year average that is mostly later, bigger months, and late months look stronger than they are.

Tableau

AVG({ INCLUDE YEAR([ride_date]) : SUM([rides]) / (MIN({ FIXED YEAR([ride_date]) : SUM([rides]) }) / 12) })
// MONTH([ride_date]) on Columns

SQL

WITH m AS (
  SELECT
    CAST (date_trunc('month', ride_date) AS DATE) AS month,
    SUM(rides) AS rides
  FROM input
  GROUP BY 1
), r AS (
  SELECT
    month,
    rides
      / (SUM(rides) OVER (PARTITION BY year(month)) / 12.0) AS ratio
  FROM m
)
SELECT
  month(month) AS month_of_year,
  strftime(MIN(month), '%b') AS month_name,
  AVG(ratio) AS naive_index
FROM r
GROUP BY 1
ORDER BY 1

Rows

Month of year	Month name	Ratio
1	Jan	0.5
2	Feb	0.51
3	Mar	0.75
4	Apr	0.91
5	May	1.16
6	Jun	1.27
7	Jul	1.39
8	Aug	1.37
9	Sep	1.26
10	Oct	1.03
11	Nov	0.88
12	Dec	0.97

Is October a busy month or a quiet one?

It depends on how you ask, and the common recipe gets it wrong. Compared with the average month of its own year, October runs at 1.03: a little above a typical month. Take growth out and October is 0.94, below average. January moves the other way, from 0.5 to 0.61. The ridership didn't change; the yardstick did. A year's average month is pulled up by the bigger months late in that year, so in a growing business every early month looks weak and every late month looks strong.

Common mistake

Comparing months with the year they sit in

In a growing business, the year's average month is pulled toward the later, bigger months. Early months then look too weak and late months too strong. Divide by a trend that moves with the month instead.

WrongStarts with 2022-01-01, 2022-02-01, 2022-03-01…

Point at the line to read it.

Tableau

SUM([rides]) / (MIN({ FIXED YEAR([ride_date]) : SUM([rides]) }) / 12)

SQL

WITH m AS (
  SELECT
    CAST (date_trunc('month', ride_date) AS DATE) AS month,
    SUM(rides) AS rides
  FROM input
  GROUP BY 1
)
SELECT
  month,
  rides
    / (SUM(rides) OVER (PARTITION BY year(month)) / 12.0) AS ratio
FROM m

RightStarts with 2022-01-01, 2022-02-01, 2022-03-01…

Point at the line to read it.

Tableau

SUM([rides]) / ((WINDOW_AVG(SUM([rides]), -6, 5) + WINDOW_AVG(SUM([rides]), -5, 6)) / 2)

SQL

WITH m AS (
  SELECT
    CAST (date_trunc('month', ride_date) AS DATE) AS month,
    SUM(rides) AS rides
  FROM input
  GROUP BY 1
)
SELECT
  month,
  rides
    / ((AVG(rides) OVER (ORDER BY month ROWS BETWEEN 6 PRECEDING AND 5 FOLLOWING) + AVG(rides) OVER (ORDER BY month ROWS BETWEEN 5 PRECEDING AND 6 FOLLOWING)) / 2) AS ratio
FROM m

Step 3

Draw the trend with a centered 12-month average

Twelve months have no middle month, so the classical trend averages two 12-month windows, one shifted a month, which gives the months at each end half weight. Every window holds each calendar month exactly once, so the season cancels and only the trend is left. The first and last six months have no full window and stay blank.

Tableau

IF INDEX() > 6 AND INDEX() <= SIZE() - 6 THEN (WINDOW_AVG(SUM([rides]), -6, 5) + WINDOW_AVG(SUM([rides]), -5, 6)) / 2
END
// Compute Using: ride_date (month)

SQL

WITH m AS (
  SELECT
    CAST (date_trunc('month', ride_date) AS DATE) AS month,
    SUM(rides) AS rides
  FROM input
  GROUP BY 1
)
SELECT
  month,
  rides,
  CASE
    WHEN ROW_NUMBER() OVER (ORDER BY month) > 6 AND ROW_NUMBER() OVER (ORDER BY month) <= COUNT(*) OVER () - 6 THEN (AVG(rides) OVER (ORDER BY month ROWS BETWEEN 6 PRECEDING AND 5 FOLLOWING) + AVG(rides) OVER (ORDER BY month ROWS BETWEEN 5 PRECEDING AND 6 FOLLOWING)) / 2
    END AS trend
FROM m
ORDER BY month

Rows

Month	Rides	Trend
2022-01-01	14,665	–
2022-02-01	15,131	–
2022-03-01	23,265	–
2022-04-01	27,316	–
2022-05-01	35,859	–
2022-06-01	38,498	–
2022-07-01	41,062	30,124
2022-08-01	41,458	30,748
2022-09-01	34,184	31,433
2022-10-01	31,334	32,353
2022-11-01	26,384	33,491
2022-12-01	28,352	34,847
2023-01-01	22,622	36,434
2023-02-01	22,155	38,022
2023-03-01	32,690	40,488
2023-04-01	39,968	42,787
2023-05-01	50,508	43,898
2023-06-01	56,395	45,064
2023-07-01	61,251	46,095
2023-08-01	59,387	46,984
2023-09-01	75,443	48,084
2023-10-01	45,236	49,498
2023-11-01	39,160	51,332
2023-12-01	43,544	53,355
2024-01-01	32,186	55,569
2024-02-01	33,925	58,092
2024-03-01	47,315	59,281
2024-04-01	59,286	60,008
2024-05-01	75,204	61,603
2024-06-01	80,258	63,219
2024-07-01	90,506	64,75
2024-08-01	90,688	66,004
2024-09-01	72,679	67,567
2024-10-01	65,442	69,604
2024-11-01	57,238	72,005
2024-12-01	64,256	74,991
2025-01-01	48,211	78,283
2025-02-01	47,998	81,454
2025-03-01	70,769	84,187
2025-04-01	84,720	86,738
2025-05-01	107,383	89,093
2025-06-01	119,756	91,116
2025-07-01	130,013	–
2025-08-01	127,277	–
2025-09-01	101,679	–
2025-10-01	97,667	–
2025-11-01	81,533	–
2025-12-01	88,510	–

Step 4

Divide each month by its trend

Now growth is gone: the same wave repeats at the same height every year. One spike sticks out, September 2023, the free-ride week.

Tableau

SUM([rides]) / [Trend]

SQL

WITH m AS (
  SELECT
    CAST (date_trunc('month', ride_date) AS DATE) AS month,
    SUM(rides) AS rides
  FROM input
  GROUP BY 1
), t AS (
  SELECT
    month,
    rides,
    ROW_NUMBER() OVER (ORDER BY month) AS k,
    COUNT(*) OVER () AS n,
    (AVG(rides) OVER (ORDER BY month ROWS BETWEEN 6 PRECEDING AND 5 FOLLOWING) + AVG(rides) OVER (ORDER BY month ROWS BETWEEN 5 PRECEDING AND 6 FOLLOWING))
      / 2 AS trend
  FROM m
)
SELECT month, rides / trend AS ratio_to_trend
FROM t
WHERE k > 6 AND k <= n - 6
ORDER BY month

Rows

Month	Ratio to trend
2022-07-01	1.36
2022-08-01	1.35
2022-09-01	1.09
2022-10-01	0.97
2022-11-01	0.79
2022-12-01	0.81
2023-01-01	0.62
2023-02-01	0.58
2023-03-01	0.81
2023-04-01	0.93
2023-05-01	1.15
2023-06-01	1.25
2023-07-01	1.33
2023-08-01	1.26
2023-09-01	1.57
2023-10-01	0.91
2023-11-01	0.76
2023-12-01	0.82
2024-01-01	0.58
2024-02-01	0.58
2024-03-01	0.8
2024-04-01	0.99
2024-05-01	1.22
2024-06-01	1.27
2024-07-01	1.4
2024-08-01	1.37
2024-09-01	1.08
2024-10-01	0.94
2024-11-01	0.79
2024-12-01	0.86
2025-01-01	0.62
2025-02-01	0.59
2025-03-01	0.84
2025-04-01	0.98
2025-05-01	1.21
2025-06-01	1.31

Step 5

Average the ratios per calendar month

Side by side, the simple index tilts the whole curve toward the end of the year: winter looks deeper and autumn looks busier than they are. In Tableau this is a nested table calculation; set each level's Compute Using in Edit Table Calculation.

Tableau

WINDOW_AVG([Ratio to trend])
// YEAR and MONTH of ride_date on the view. Ratio to trend: Compute Using Year, Month. This calc: Compute Using Year, restarting every Month

SQL

WITH m AS (
  SELECT
    CAST (date_trunc('month', ride_date) AS DATE) AS month,
    SUM(rides) AS rides
  FROM input
  GROUP BY 1
), t AS (
  SELECT
    month,
    rides,
    ROW_NUMBER() OVER (ORDER BY month) AS k,
    COUNT(*) OVER () AS n,
    (AVG(rides) OVER (ORDER BY month ROWS BETWEEN 6 PRECEDING AND 5 FOLLOWING) + AVG(rides) OVER (ORDER BY month ROWS BETWEEN 5 PRECEDING AND 6 FOLLOWING))
      / 2 AS trend,
    rides
      / (SUM(rides) OVER (PARTITION BY year(month)) / 12.0) AS year_ratio
  FROM m
)
SELECT
  month(month) AS month_of_year,
  strftime(MIN(month), '%b') AS month_name,
  'Simple' AS method,
  AVG(year_ratio) AS seasonality_index
FROM t
GROUP BY 1
UNION ALL 
SELECT
  month(month),
  strftime(MIN(month), '%b'),
  'Growth removed',
  AVG (rides / trend)
FROM t
WHERE k > 6 AND k <= n - 6
GROUP BY 1
ORDER BY 1, 3 DESC

Rows

Month of year	Month name	Method	Year ratio
1	Jan	Simple	0.5
1	Jan	Growth removed	0.61
2	Feb	Simple	0.51
2	Feb	Growth removed	0.59
3	Mar	Simple	0.75
3	Mar	Growth removed	0.82
4	Apr	Simple	0.91
4	Apr	Growth removed	0.97
5	May	Simple	1.16
5	May	Growth removed	1.19
6	Jun	Simple	1.27
6	Jun	Growth removed	1.28
7	Jul	Simple	1.39
7	Jul	Growth removed	1.36
8	Aug	Simple	1.37
8	Aug	Growth removed	1.33
9	Sep	Simple	1.26
9	Sep	Growth removed	1.24
10	Oct	Simple	1.03
10	Oct	Growth removed	0.94
11	Nov	Simple	0.88
11	Nov	Growth removed	0.78
12	Dec	Simple	0.97
12	Dec	Growth removed	0.83

How do I measure seasonality when the business is growing?

Riverside Bikes, daily rides 2022 to 2025

Total rides per month

The simple index: each month against its year

Is October a busy month or a quiet one?

Comparing months with the year they sit in

Draw the trend with a centered 12-month average

Divide each month by its trend

Average the ratios per calendar month

What does one bad month do to the index?

Using a trailing 12-month average as the trend

Run it on your own data

Questions

How much history do I need?

What do I do with the index once I have it?

Why do my twelve indices not average exactly 1?

Can I do day-of-week seasonality the same way?

Sources