How to do a year to date, year over year comparison in Looker Studio?
Learn to do a proper year to date, year over year comparison in Looker Studio by adding a couple of calculated fields, as easy as that !
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, …
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:
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.
Please Edit your Data Source, and then Add a field. In my example, I’ll be looking at Sessions in a GA4 property from a third-party connector (beware, won’t work on the native connector). 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:
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
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:
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à!
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 !
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…):