How to make a same day last year comparaison in Looker Studio?
Tired of comparing apples to oranges? Need to make a proper Monday to same Monday last year comparison? Check this out!
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/2025 versus 22/05/2024) instead of comparing Monday week 23 2025 versus Monday week 23 2024. 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.
Looker Studio connectors we use on a daily basis and are happy with (w/ free trials): Supermetrics - Windsor - Catchr - PMA - Funnel
Not sure which one to pick? Reviews here and there. Still unsure?! Contact us with your tech stack and budget, and we’ll guide you in the right direction:
Create the Blended Data
First of, please create a new Blend by clicking on Resources -> Manage Blends in the top ribbon:
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:
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) (you could also use EXTRACT(ISOWEEK from Date), a different way of categorizing weeks)
A third calculated field called Weekday using the formula: WEEKDAY(Date)
This is what you should see:
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) (or EXTRACT(ISOWEEK from DATE) if you went with this one)
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:
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 2025 Vs Week 23 2024, what you initially wanted to do :)
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 have to make sure to select Jan 1st 2024 — Dec 31st 2025 for instance so you’ve got all 2024 & 2025, and we want to avoid that. If you want to compare 2025 Vs 2024, just select Jan 1st 2025— Dec 31st 2025 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 Today Minus 3 Years — Today Minus 1 Days (I’m proposing to take 3 years in the past, feel free to update this).
Give a name to your blend, use it as a data source in your data visualization, and you should be all set.
PROBLEM SOLVED !
Tools we use and we like:
Todoist: for tasks management
Monday: for projects management, when tasks fall short
Tropical Server: our friends in charge of the hosting of this very website!
Tools we’d like to use (if we had the budget…):