How to make a same day last year comparison in Looker Studio?


If you've landed here, it's because you are facing a very classic issue with Google Looker Studio (formerly known as Data Studio) Year over Year analysis. No worries, we've got the solution for you.

So, you're trying to make a day versus same day last year comparison, but Looker Studio compares dates (ie 22/05/2022 versus 22/05/2021) instead of comparing Monday week 23 2022 versus Monday week 23 2021. How can you do that in an elegant manner?

Using Blended Data my dear Watson!

We're going to use a GA4 property and the classic sessions metric to explain you the solution, but it does work with any data source that is having a date field.


Need help ? We offer Looker Studio Support for companies.

Looking for a connector ? Check Supermetrics.


Create the Blended Data

First of, please create a new Blend by clicking on Resources -> Manage Blends in the top ribbon:

Looker Studio Manage Blends

Add a blend

Now click on Add a Blend. We're going to choose our GA4 property, rename it "This Year", and "self-join" to itself by clicking on Join another table and choose the same GA4 property, called "Last Year". At this stage you should have something like that:

Looker Studio Blend GA4 property

Set up dimensions and metrics

In Table 1, we are going to add 4 dimensions:

  • Date
  • A first calculated field called Year using the following formula: YEAR(Date)+0 accessible by clicking Add dimension -> Create field
  • A second calculated field called Week using the formula: WEEK(Date)
  • A third calculated field called Weekday using the formula: WEEKDAY(Date)


This is what you should see:

Looker Studio Adding dates field

As for the metrics, you can use Sessions and rename it to Sessions this year.

Now, moving on the Last year Table 2. This time, first we'll add Sessions to metrics, renaming it to Sessions last year.

And the Dimension fields will now be:

  • A first calculated field called Year using the following formula: YEAR(Date)+1. This is where the magic happens to make Year over Year comparison
  • A second calculated field called Week using the formula: WEEK(Date)
  • A third calculated field called Weekday using the formula: WEEKDAY(Date)


And voilĂ , we're one step away from completing the same day last year comparison:

Looker Studio Setting up last year date dimensions

Configure Join


We are almost there. Please click on Configure join, and as you'd expect, Left outer join Year on Year, Week on Week, Weekday on Weekday (using Left outer, I'm assuming you've got sessions everyday for both years, you might want to consider a different type of join if it's not the case).

What are we asking Looker Studio here? Well, to put Sessions last year next to Sessions this year, when week and weekday are equals, but when year differs by 1 (hence in the formula, the +0 and +1).

We are indeed doing Monday Week 23 2022 Vs Week 35 2021, what you initially wanted to do :)

Looker Studio Dates join configuration

Configure Date Range

Last but not least, we need to ensure that last year data does not depend on any date range control (if it did, it means that to make a proper comparison, you'd always to make sure to select Jan 1st 2021 - Dec 31st 2022 for instance so you've got all 2021 & 2022, and we want to avoid that. If you want to compare 2022 Vs 2021, just select Jan 1st 2022 - Dec 31st 2022 and you should expect it to work).

So we're going to take some margin, assuming you don't want to go to far away in the past, by changing the Table 2 Date range to Custom, and then select Advanced, and set-up as follow (I'm proposing to take 4 years in the past, feel free to update this):

Looker Studio Setting up table 2 date range custom

Give a name to your blend, use it as a data source in your data visualization, and you should be all set.

PROBLEM SOLVED !


Next article: How to split Analytics conversion by Search Console keywords in Looker Studio?

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