How to do a year to date, year over year comparison in Looker Studio?

This is a classic ask for Looker Studio specialists (formerly known as Data Studio specialists... SEO optimization is driving me nuts!). Your boss comes to you and asks: "I've got a meeting in 30 minutes, can you do a quick year to date, year over year comparison? Nothing fancy, just need the split by categories plus absolute and relative comparison, I'm sure it's an easy one for you".

Well, not really...


Because date range control is not so friendly when it comes to comparing two time periods. Sure, you could use the default comparison date range feature (previous year or previous period), but if you've used it you know it's not solving your issue.


In this article, we are going to create new fields in a table so you can do proper year to date, year over year comparison. Split by any dimension, compare absolute, relative, everything you've dreamt of.

Plus, once you dig it, you can apply it to any time period really: this week to date Vs same week last year to date, this month to date Vs last month to date, ... And use it on line charts, histogram charts, ...


Need help ? We offer Looker Studio Support for companies.

Looking for a connector ? Check Supermetrics.


1. Create new calculated fields in your data source

First thing we're going to do, we're going to create two new calculated fields. If you're not sure what I'm talking about, please check this article around How to build calculated fields in Looker Studio and what are the most useful formulas?

Please Edit your Data Source, and then Add a field. In my example, I'll be looking at Sessions in a GA4 property. So to get this year's sessions, I'll write the following formula:

CASE

WHEN YEAR( Date ) = YEAR( CURRENT_DATE() ) THEN Sessions

ELSE 0

END


Here, I'm just telling Looker Studio to keep only this year's sessions in my new field:

Looker Studio year to date year over year this year sessions

It's an easy one, if today's date is July 2nd, it will get all sessions from Jan 1st to July 2nd this year.


Now, how can I get the sessions from Jan 1st to July 2nd last year? Well, a nicely written formula again. Please save your fields, add another field, and type as follow:

CASE

WHEN

YEAR( Date ) = YEAR( CURRENT_DATE() ) - 1

AND

(

MONTH( Date ) < MONTH( CURRENT_DATE() )

OR 

( MONTH( Date ) = MONTH( CURRENT_DATE() ) AND DAY( Date ) <= DAY( CURRENT_DATE() ) ) 

) THEN Sessions

ELSE 0

END

Looker Studio year to date year over year last year sessions

Here, we're just telling Looker Studio the following:

  • Get last year sessions
  • If month last year is prior to current month, then keep sessions
  • If month last year is equal to current month, then keep days last year prior to current day


So if you're running these formulas on July 2nd, it will get all last year's sessions from Jan 1st to June 30th with the first condition, and with the second the July sessions prior or equal to July 2nd, so July 1st and July 2nd, exactly what we wanted!

Quick note here: I'll let you play with the < or <= signs to decide if we keep today or not.


2. Create a table and set the right default custom date range

Ok, we're not done yet. Please add a chart, table. As metrics, you can now add your sessions this and sessions last year, and as a dimension whatever you want.


But to ensure that all data is shown as expected, in the Setup ribbon, please use Custom default date range to cover last year and this year to date:

Looker Studio year to date year over year default date range control

And that's pretty much it. Now, you can Add fields directly in your table to make some calculations, like SUM( sessions this year ) - SUM( sessions last year ), and voilà!

Looker Studio year to date year over year table with comparison

In this article, you've learned how to make a year to date, year over year comparison. Now, working a bit with the formulas and the custom default date range, you should be able to compare anything you've always wanted to.

PROBLEM SOLVED !


Previous article: How to build calculated fields in Looker Studio and what are the most useful formulas?

Next article: Why you shouldn't use Pivot Tables in Looker Studio? (and what to replace them with)

Looking for something else? All our articles can be found on our blog page