Comparing time periods

Introduction

This tutorial will show you how to compare two or more time periods in your data to get results such as:

A side-by-side bar chart:

A summary table:

A bar chart of differences (or deltas):

Creating a metric

In this tutorial, we’ll use the Invoices table from the Sample Database.

First, we’ll create a metric to represent the revenue of our business:

1. Click + New to create a fresh question.
2. Select Raw Data > Sample Database > Invoices.
3. Under Summarize, click “Pick the metric you want to see”.
4. Select “Sum of…”
5. Select “Payment”.
6. Click Visualize.

The steps above should grace your screen with a large, impressive number (around 6.7M). This number is the grand total of the Payment column in the Invoices table.

A grand total calculation will always look nice and impressive, but grand totals don’t tell us whether our business is growing, shrinking, or plateauing over time.

Comparing a metric between two time periods

If we want to learn about our business’s progress, we need to calculate the change in revenue (total payments) over time.

For example, we can check if our revenue is growing, shrinking, or plateauing by comparing the total payments in 2022 with the total payments in 2021.

The recipe for a single time period comparison goes like this:

1. Calculate a metric over time period A (2022).
2. Calculate the same metric over time period B (2021).
3. Get the difference between A’s metric and B’s metric.

If we repeat this recipe for each of the years in our Invoices table, we’ll get a year-over-year (YoY) comparison. But we don’t have to pick a calendar year as our time period: if we have monthly data, we do a month-over-month (MoM) comparison, or we can even set our own custom time periods.

Calculating a metric’s performance year over year

In our initial Sum of Payment question, we took the all-time grand total of the Payment column. To do a year-over-year (YoY) calculation, we’re going to:

1. Sum payments received in 2022.
2. Sum payments received in 2021.
3. Find the YoY change in total payments (revenue) from 2021 to 2022 in a few formats:
   • Bar chart visualization
   • Dollar difference
   • Percentage difference

Total payments in 2022

You can start the YoY calculation from the original “Sum of payment” question if you’ve still got it open. Otherwise, click + New to create a fresh question.

To get the total of payments received in 2022:

1. Open the query builder.
2. Under Summarize, click “Sum of Payment”.
3. In the popup menu, click the back arrow (<).
4. Click Custom Expression at the bottom of the menu (scroll down if needed).

5. Enter the following SUMIF expression:

    SumIf([Payment], between([Date Received], "2022-01-01", "2022-12-31"))
   

6. Name the custom expression “Total Payments 2022”.

The sumif formula will add up payments that have a “Date Received” between Jan 1, 2022 and Dec 31, 2022.

Total payments in 2021

Next, we’ll add a second custom expression to get the total of payments received in 2021:

1. Under Summarize, click + to add another metric.
2. Click Custom Expression at the bottom of the popup menu.

3. Enter the following expression:

    SumIf([Payment], between([Date Received], "2021-01-01", "2021-12-31"))
   

4. Name the custom expression “Total Payments 2021”.

This sumif expression will add up payments that have a “Date Received” between Jan 1, 2021 and Dec 31, 2021.

Tip: you can shorten the expression as

SumIf([Payment], year([Date Received]) = "2021")

Click Visualize to get the results:

Visualizing YoY change as a bar chart

To get a sense of the “size” of the change in revenue YoY, we can create a side-by-side bar chart visualization. We’ll also break out the YoY change by calendar month, so we can see whether the change in revenue is seasonal.

1. Click Summarize at the top right of the question results from Total payments in 2021.
2. Under Group by, select “Date Received: by month of year”.
3. Click the bar chart icon at the bottom of the question results page.

Optional visualization settings:

1. To reorder the legend chronologically, click Settings and drag Total Payments 2021 above Total Payments 2022.
2. To make sure the bars are aligned to the same axis, click Settings > Axes and toggle off “Split y-axis when necessary”.

The side-by-side bar chart creates a visual YoY comparison for each calendar month. In this example, there’s great YoY growth in the first half of 2022 (compared to 2021), and slight YoY decline in the second half of 2022.

For more ways to visualize time period comparisons, check out Comparing time series.

Calculating YoY change in dollars

In our side-by-side bar chart, the YoY difference is large enough for us to notice growth and decline. But what if the YoY delta is small, and the height of the bars is too close together for us to tell the difference between growth and decline? In some cases, it’s more useful to look at change using values (like dollars) or percentages.

To get the YoY change in revenue from 2021 to 2022, we’ll start a fresh question (+ New) using the Invoices table.

1. Under Summarize, click “Pick the metric you want to see”.
2. Click Custom Expression at the bottom of the menu (scroll down if needed).

3. Subtract the Total payments in 2021 formula from the Total payments in 2022 formula by putting a minus sign between each formula:

    SumIf([Payment], between([Date Received], "2022-01-01", "2022-12-31")) - SumIf([Payment], between([Date Received], "2021-01-01", "2021-12-31"))
   

4. Name the custom column “Payment Dollar Change (2021 to 2022)”.

Calculating YoY change as a percentage

We’ll add the percentage change in revenue from 2021 to 2022 to the same question.

1. Under Summarize, click + to add another metric.
2. Click Custom Expression at the bottom of the popup menu.
3. Divide the YoY dollar difference formula by the Total payments in 2021 formula to get the percentage change in revenue:

(
  SumIf([Payment], between([Date Received], "2022-01-01", "2022-12-31")) - SumIf([Payment], between([Date Received], "2021-01-01", "2021-12-31"))
  ) /
SumIf([Payment], between([Date Received], "2021-01-01", "2021-12-31")) * 100

Click Visualize to get percentage difference side-by-side with the dollar difference.

Overall, we saw YoY growth of 234% from 2021 to 2022, so it seems like our business is doing really well, even though our YoY by month bar chart displayed revenue decline in the second half of the year.

To visualize the dollar change or percentage change as a bar chart:

1. Click Summarize at the top right of the question results.
2. Under Group by, select “Date Received: by month of year”.
3. Click the X beside Payment Dollar Change (2021 to 2022) remove the dollar change values from the chart.

In other words, our business’s YoY growth is being driven by the payments received in the first half of the year. We’re working with sample data, so there’s no particular reason for the growth here, but if you saw this increase in real life, you might want to look into possible reasons, such as seasonal effects or campaigns.

Calculating a metric’s performance month over month

To do a month-over-month (MoM) calculation, we’re going follow the same time period comparison recipe, but with modified sumif expressions:

1. Sum payments received in Oct 2022.
2. Sum payments recieved in Sep 2022.
3. Find the MoM change in total payments (revenue) from Sep 2022 to Oct 2022 in different formats:
   • Bar chart visualization
   • Dollar difference
   • Percentage difference

Total payments in Oct 2022

Start a fresh question using the Invoices table. From the query builder:

1. Under Summarize, click “Pick the metric you want to see”.
2. Click Custom Expression at the bottom of the menu (scroll down if needed).
3. Enter a sumif expression with a new set of dates:

SumIf([Payment], between([Date Received], "2022-10-01", "2022-10-31"))

This expression will sum payments for rows with “Date Received” between Oct 1, 2022 and Oct 31, 2022.

Tip: you can also write the expression as

SumIf([Payment], year([Date Received]) = "2021" AND month([Date Received]) = "10")

Total payments in Sep 2022

Continuing with the question from Total payments in Oct 2022:

1. Under Summarize, click + to add another metric.
2. Click Custom Expression at the bottom of the popup menu.
3. Enter the following expression:

SumIf([Payment], between([Date Received], "2022-09-01", "2022-09-30"))

Visualizing MoM change as a bar chart

Once you’ve created custom expressions for the Total payments in Oct 2022 and the Total payments in Sep 2022, go to Summarize and group by “Date Received: by day of month”.

Calculating MoM change in dollars

To calculate the MoM change in dollars, subtract Total payments in Sep 2022 from Total payments in Oct 2022:

SumIf([Payment], between([Date Received], "2022-10-01", "2022-10-31")) - SumIf([Payment], between([Date Received], "2022-09-01", "2022-09-30"))

Calculating MoM change as a percentage

To calculate the MoM change as a percentage, subtract Total payments in Sep 2022 from Total payments in Oct 2022 and divide the whole thing by Total payments in Sep 2022:

(
  SumIf([Payment], between([Date Received], "2022-10-01", "2022-10-31")) - SumIf([Payment], between([Date Received], "2022-09-01", "2022-09-30"))
  ) /
SumIf([Payment], between([Date Received], "2022-09-01", "2022-09-30")) * 100

Remember, you can visualize the dollar or percentage change in a bar chart by going to Summarize and grouping by “Date Received: by day of month”.

Calculating a metric’s performance over custom time periods

You don’t have to use calendar years or calendar months as your time periods—you can fill in the sumif formulas with custom time periods that match your business, such as a season or fiscal year.

For example, to look at the change in revenue between Spring 2021 and Spring 2022:

1. Create a sumif expression with dates for Spring 2022:

    SumIf([Payment], between([Date Received], "2022-03-01", "2022-05-30"))
   

2. Create a sumif expression with dates for Spring 2021:

    SumIf([Payment], between([Date Received], "2021-03-01", "2022-05-30"))
   

3. Find the difference between (1) and (2) using your format of choice: bar chart, dollar change, or percentage change.

To ensure fair time period comparisons, your time periods should be of similar or (ideally) equal duration. For example, in our MoM example, we might observe a positive change from Sep 2022 to Oct 2022 simply because the month of October has an extra day for payments compared to the month of September.

You’ll also want to make note of any circumstances that could affect a period’s results. For example, the change in your revenue could be affected by a change in people’s spending habits on weekends or holidays.

More ways to compare time periods

The methods covered in this tutorial work best with 2-3 time periods. If you wanted to calculate the MoM change in dollars or percentages for 24 months of data, you’d have to repeat the MoM time period comparisons 23 times! Once you’re comfortable with time series comparisons, check out Trend analysis: measuring rates of change for a faster way to calculate deltas across all of your time periods at once.

Further reading

• Visualizing trends
• Dates in SQL
• Calculating LTV