Chart guide Tableau and SQL

How do I build a KPI card that compares this month with last month?

Anchor the card to the latest date in the data with { FIXED : MAX([Date]) }, then compare this month to date with the same days of the prior month, not the whole prior month. Here the naive card says bookings fell 41%; the fair one says they grew 4%.

Updated October 30, 2026

The data

Lantern Yoga daily classes, Sep 2024 to 17 Oct 2025

Synthetic datastudio_days.csv

Three yoga studios in one city. One row per studio per day: classes booked, revenue and late cancellations. The data ends on Friday 17 October 2025.

`class_date`	`studio`	`bookings`	`revenue`	`late_cancels`
2024-09-01	Riverside	58	1142.4	4
2024-09-01	Old Town	73	1265.4	4
2024-09-01	Hillcrest	37	626.4	1
2024-09-02	Riverside	81	1612.8	1

Step 1

Total by month: the last bar looks like a crash

October is only 17 days old, so its bar is short. A card built on this, "October vs September", reports a collapse that isn't happening.

Tableau

SUM([bookings])
// MONTH([class_date]) on Columns

SQL

SELECT
  CAST (date_trunc('month', class_date) AS DATE) AS month,
  SUM(bookings) AS total
FROM input
GROUP BY 1
ORDER BY 1

Rows

Month	Bookings
2024-09-01	5,926
2024-10-01	6,679
2024-11-01	6,015
2024-12-01	5,303
2025-01-01	7,432
2025-02-01	6,525
2025-03-01	6,891
2025-04-01	6,505
2025-05-01	6,203
2025-06-01	5,845
2025-07-01	5,853
2025-08-01	6,238
2025-09-01	7,120
2025-10-01	4,204

Step 2

Find the latest date in the data

Every period on the card is measured from this one date, so the card updates itself when a new file arrives.

Latest date

Oct 17, 2025

Tableau

{ FIXED : MAX([class_date]) }
// name it [Latest date]

SQL

SELECT MAX(class_date) AS latest_date
FROM input

Rows

Class date
2025-10-17

Step 3

Compare the same days, not the whole month

1 to 17 October against 1 to 17 September. Now both bars cover the same number of days, and the comparison is fair.

Tableau

// [Current MTD]
SUM(IF DATETRUNC('month', [class_date]) = DATETRUNC('month', [Latest date])
    THEN [bookings] END)
// [Prior MTD]
SUM(IF DATETRUNC('month', [class_date]) = DATEADD('{knob.compare}', -1, DATETRUNC('month', [Latest date]))
       AND DAY([class_date]) <= DAY([Latest date])
    THEN [bookings] END)

SQL

WITH b AS (
  SELECT MAX(class_date) AS latest
  FROM input
)
SELECT
  'This month to date' AS period,
  SUM(bookings) FILTER (WHERE date_trunc('month', class_date) = date_trunc('month', latest)) AS total
FROM input, b
UNION ALL 
SELECT
  'Same days, one {knob.compare} earlier',
  SUM(bookings) FILTER (WHERE date_trunc('month', class_date) = date_trunc('month', latest) - INTERVAL 1 month AND day(class_date) <= day(latest))
FROM input, b

Rows

Period	Total
This month to date	4,204
Same days, one month earlier	4,043

Step 4

Assemble the card: headline, change, direction

Big number first, the change right under it, and the prior value small so nobody has to guess what it's compared with. Switch the measure to late cancellations: the arrow goes up and the color turns red, because more cancellations is bad news.

This month to date

4,204

+4% vs 4,043

Tableau

// [Change %]
([Current MTD] - [Prior MTD]) / [Prior MTD]
// Text: [Current MTD] large, [Change %] below it
// Color: [Change %] > 0, flipped for measures where up is bad

SQL

WITH b AS (
  SELECT MAX(class_date) AS latest
  FROM input
), p AS (
  SELECT
    SUM(bookings) FILTER (WHERE date_trunc('month', class_date) = date_trunc('month', latest)) AS CURRENT,
    SUM(bookings) FILTER (WHERE date_trunc('month', class_date) = date_trunc('month', latest) - INTERVAL 1 month AND day(class_date) <= day(latest)) AS prior
  FROM input, b
)
SELECT
  CURRENT,
  prior,
  CURRENT - prior AS delta,
  CURRENT / prior - 1 AS change
FROM p

Rows

Current	Prior	Delta	Change
4,204	4,043	161	4%

Why does a month-over-month card always look bad early in the month?

Because it compares a few days with a whole month. On 17 October, Lantern Yoga's "October vs September" card says bookings are down 41%. Cut September at the 17th too and bookings are up 4%: 4,204 against 4,043. Against the same 17 days last year they're up 15.4%. The studio didn't have a bad month; the card did.

In Tableau

The shelves for the finished chart

Marks Text

Text[Current MTD][Change %][Prior MTD]
Color[Change %] > 0

Columnsempty

Rowsempty

Green pills are continuous, blue are discrete, as in Tableau.

Which comparison should the card use?

Use last month when people act weekly and want to know if things are moving. Use last year when the business is seasonal, like a gym in January or a yoga studio after the summer: a month-over-month change mostly measures the calendar. Turn the comparison knob and watch the same October tell two stories.

Compare with the same days of

Point at the line to read it.

Is up always good?

Not for every measure. More bookings is good news; more late cancellations is not. Switch the card to late cancellations: they're up 25.1%, and the card turns red, because the color follows whether the change is good for the business, not which way the arrow points.

Measure

Point at the line to read it.

Variants

Other ways to draw it

With a 30-day sparkline

A thin line of the last 30 days under the number shows whether the change is a trend or one good week. Hide its axes and gridlines.

Point at the line to read it.

One card per studio

Put studio on Columns to get a row of cards from the same formulas.

Riverside

1,788

+5.2% vs 1,699

Old Town

1,305

−2.7% vs 1,341

Hillcrest

1,111

+10.8% vs 1,003

Common mistake

Comparing a part month with a whole month

17 days of October against 30 days of September will always look like a drop, every month, until the last day. Cut the prior period at the same day of the month.

WrongStarts with October to date, September…

Point at a bar to read it.

Tableau

SUM(IF DATETRUNC('month', [class_date]) = DATETRUNC('month', [Latest date]) THEN [bookings] END) / SUM(IF DATETRUNC('month', [class_date]) = DATEADD('month', -1, DATETRUNC('month', [Latest date])) THEN [bookings] END) - 1

SQL

WITH b AS (
  SELECT MAX(class_date) AS latest
  FROM input
)
SELECT
  'October to date' AS period,
  SUM(bookings) FILTER (WHERE date_trunc('month', class_date) = date_trunc('month', latest)) AS bookings
FROM input, b
UNION ALL 
SELECT
  'September',
  SUM(bookings) FILTER (WHERE date_trunc('month', class_date) = date_trunc('month', latest) - INTERVAL 1 month)
FROM input, b

RightStarts with October to date, September 1 to 17…

Point at a bar to read it.

Tableau

SUM(IF DATETRUNC('month', [class_date]) = DATETRUNC('month', [Latest date]) THEN [bookings] END) / SUM(IF DATETRUNC('month', [class_date]) = DATEADD('month', -1, DATETRUNC('month', [Latest date])) AND DAY([class_date]) <= DAY([Latest date]) THEN [bookings] END) - 1

SQL

WITH b AS (
  SELECT MAX(class_date) AS latest
  FROM input
)
SELECT
  'October to date' AS period,
  SUM(bookings) FILTER (WHERE date_trunc('month', class_date) = date_trunc('month', latest)) AS bookings
FROM input, b
UNION ALL 
SELECT
  'September 1 to 17',
  SUM(bookings) FILTER (WHERE date_trunc('month', class_date) = date_trunc('month', latest) - INTERVAL 1 month AND day(class_date) <= day(latest))
FROM input, b

Common mistake

Green for up, whatever the measure

Late cancellations rose 25.1%. A card that colors every increase green congratulates the studio on it. Decide per measure whether up is good, and flip the color for the ones where it isn't.

Wrong

Tableau

Color: [Change %] > 0
// green when true, for every card

Right

Tableau

Color: IF [Up is good] THEN [Change %] > 0
ELSE [Change %] < 0
END

Common mistake

Anchoring to TODAY() instead of the data

If today's load is late, TODAY() starts a day the data doesn't have, and the current period comes up a day short. The latest date in the data never runs ahead of the rows.

Wrong

Tableau

DATETRUNC('month', TODAY())

Right

Tableau

DATETRUNC('month', { FIXED : MAX([class_date]) })

Run it on your own data

Any table with a date and a measure works: daily sales, signups, tickets. Drop your CSV, pick the date and the measure, and the card anchors itself to your latest date in your browser. Nothing is uploaded.

Drop a CSV anywhere on this page

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

Questions

Why use the latest date in the data instead of TODAY()?

Data lands late. If yesterday's file hasn't arrived, TODAY() counts a day the data doesn't have yet and the current period looks short. MAX of the date column always matches what is actually loaded.

Why does { FIXED : MAX([Date]) } ignore my region filter?

FIXED expressions run before ordinary dimension filters. If the latest date should follow a filter, add that filter to context (right-click it, Add to Context).

How do I put four KPI cards in one row?

Build one sheet per card and place them side by side in a horizontal dashboard container. Or put Measure Names on Columns and Measure Values on Text, filtered to your headline measures.

Should the card show a percent or an absolute change?

Show the percent when the prior value is a normal size, and the absolute change when it's tiny: going from 2 to 6 is +200%, which says more about the base than the business.

Sources

Tableau: FIXED level of detail expressions checked October 30, 2026
Tableau: Date functions checked October 30, 2026