How to Create Custom Formatted Date Comparisons in Google Data Studio

By: Pat Strickler

If you have been using Data Studio for any length of time, it’s very likely that you have run into limitations around date comparisons. By default, you get some decent options for comparing to a previous period, previous year, or customer period, but the way these comparisons are visualized isn’t always ideal.

For instance, the chart below is the default line chart that Data Studio pumps out with a date comparison.

There are no options to edit the color of the comparison line and (most annoyingly) there is no way to force it behind the line of your primary period. These two limitations alone led me to develop a custom (and I’ll admit pretty hacked together) way of displaying comparison periods.

Like any proper baking show, I’ll show you the end result first. Below is the same line chart visual except our comparison period is placed behind our primary period and I was able to adjust the color of the line to my liking.

If you’re interested in creating this for yourself and unlocking even more use cases, then I encourage you to follow the tutorial below:

Step 1: Connect to Any Dataset with a Date Field

I will be using Google Analytics for my example, but this works with any dataset that has a Date field and a wide enough range of data for a comparison period. I’ll note here that this method can be adapted for any comparison period, but year over year (Y/Y) is most ideal, and that’s what I will be showing here.

Step 2: Create a Comparison Date Field in your Dataset

We will need to create another field in our dataset that contains the date we want to compare to. This is difficult to explain in text, so this table below is what we’re attempting to create for a Y/Y comparison:

We will need to use a variety of formulas to create this ‘Comparison Date’ field. Ideally we could do this in 1 formula, but Data Studio is very particular with what it will allow you to do in 1 formula, so let’s create the following fields:

Previous Year: This will convert our current year to our previous year (e.g. 2020 to 2019). IMPORTANT: Convert this field to TEXT if not automatically converted.

YEAR(Date)-1

Month*: We need this field to create our final date. I’m using the * character to denote that this is a custom field in case Month already exists in your dataset. IMPORTANT: Convert this field to TEXT if not automatically converted.

MONTH(Date)

Month**: Data Studio is kind of dumb, so we need to convert any Month* values that are single digits to double digits. IMPORTANT: Convert this field to TEXT if not automatically converted.

CASE
WHEN Month*='1' THEN '01'
WHEN Month*='2' THEN '02'
WHEN Month*='3' THEN '03'
WHEN Month*='4' THEN '04'
WHEN Month*='5' THEN '05'
WHEN Month*='6' THEN '06'
WHEN Month*='7' THEN '07'
WHEN Month*='8' THEN '08'
WHEN Month*='9' THEN '09'
ELSE Month*
END

Day*: Similar to the Month* field, we need to Day* to create our final comparison date. Again I’m using the * character to keep it unique from any existing Day fields. IMPORTANT: Convert this field to TEXT if not automatically converted.

DAY(Date)

Comparison Period Concat: We need to concatenate our comparison date pieces together into a single string. IMPORTANT: Convert this field to TEXT if not automatically converted.

CONCAT(CONCAT(Previous Year, Month**), Day*)

Comparison Period as Date: Lastly, we need to convert our Comparison Period Concat field into a Date.

TODATE(Comparison Period Concat,'DEFAULT_DECIMAL','%Y%m%d')

Viola, you now have your Comparison Period field created and ready for the next step: blending!

Step 3: Blend Your Dataset with Itself

We need to blend our dataset with itself using the Comparison Period field we just created as our primary key. This bit might be a little confusing, but we’re trying to achieve the following:

To do this, we need to create the following blended dataset:

Under Resource, click Manage blended data, then click Add a Data View
Select the dataset you have been working with (in my case Google Analytics) and set your Comparison Period as Date field as your Join Key. Note: the Join Key option will appear once you connect another dataset, so just make sure it’s your first Dimension for now.

Next, click and drag your regular Date field into the Dimensions space. I like to label this as Current Date so I don’t get confused later on.
For Metrics, choose whatever values you will want to compare (e.g. Sessions, Pageviews, Bounces, etc.). In my case, I’m just going to compare Sessions, so I’ll drag that into the Metrics space. You will also want to rename each Metric so it’s clear which period it aligns with. In my case, I’m going to label Sessions as Sessions this Year.

I’ve run into some trouble with the Date Range selector when trying to set this up, but I think the safest way to do this is to select an advanced date range of minus 11 months to minus 0 months.

OK, now we need to blend this with itself to get our comparison period data. Click Add Another Data Source and choose the same dataset you were just working with.
For your Join Key choose the regular Date field (not the Comparison Period as Date field)
Next you’ll need to match all of the Metrics that you chose to compare in your primary dataset. In my case, I’m going to choose Sessions. Again, label your Metrics so they align with your comparison period. In my case, I will label my Sessions metric as Sessions last Year.

Again, the Date Range selector can be a little finicky. I found success choosing minus 23 months to minus 12 months for a Y/Y comparison.
OK, once you have all your Metrics selected, you can click Save.

Step 4: Create Your Visual

To complete this process, let’s create a line chart visualization to display our two time periods.

Add a Line Chart visual to your dashboard.
Make sure the Blended Dataset you created in step 3 is set as your datasource.
For your Date Dimension choose the Current Date (not Comparison Period as Date)
For your Metrics to display, choose your Metric last Year first and Metric this Year second. In my case, this will be Sessions last Year followed by Sessions this Year. The order matters here because it will dictate which line is on top of the other. Setting it up this way will force your ‘last Year’ line behind your ‘this Year’ line, which is what we want.

Lastly, you can adjust the format of each line individually. I like to set my ‘this Year’ metric to something that pops like blue, and keep all my ‘last Year’ metrics in a more muted gray color.

Feel free to tweak the visual to your liking and experiment with other formatting options, but if you should have something that looks like this once you’re done.

CompassRed is a full-service data agency that specializes in providing data strategy for clients across multiple industries.