How to show the “Last 6 months” in a line chart with Google Data Studio.
When working with Google Data Studio client regularly asked me to display line chart showing data for the last six months. Even if the request seems pretty reasonable, it is not so easy to answer it. However there is nothing impossible with the help of a google spreadsheet and some formula.
The problem with showing the last 6 months data in Google Data Studio
Looking at the built-in date range selector in Google Data Studio you have everything… last 14 days, last 30 days, this quarter, last quarter, This years, last years, yesterday, last month, this week, last week… but nothing like last 6 months or even last 3 months…
You can always select a specific date range manually or applying a filter to exclude and/or include a particular period but this process can’t be automated and will need to be updated every day.
How to create a last 6 months custom dimension
To solve this issue, we will have to create custom dimension directly integrated into our data set. In the case of Google Analytics for example, it means that we won’t use the direct connection between Google Analytics and Google Data Studio but an indirect connection with Google Sheet as an intermediate. Concretely we will import Google Analytics data into a Google Spreadsheet (using the free Google Analytics Add-On or Supermetrics). Then we will use this spreadsheet as our primary source.
Let say we have imported Google Analytics date and sessions in a Google Spreadsheet using the free Google Analytics Add-On. We have two columns : a date columns and a sessions column. We will add to our data set a new column named “Period” using the following formula :
Let’s break down this formula :
- Arrayformula is just here to help us expand our formula automatically for all the rows in our spreadsheet. (more about it here)
- EDATE(today(),-6) give us today’s date minus 6 months.
- if statement allows us to compare the today’s date minus 6 months with the date value on column A.
Depending on the result of the if statement each row of data will be placed in two distinct buckets: last 6 months or older than the last 6 months. Since our formula is based on today’s date comparison, the last 6 months date range period will always be automatically updated every time our data set is being refreshed. When adding our Google Spreadsheet as a new source in Google Data Studio we now have access to our new custom dimension:
You can easily use this new dimension to build a table (and incidentally make sure our calculation are correct) :
or finally creating our line chart showing the last 6 months of data:
If you have a question about this article please use the comment section below (and please especially if you found a better method !). If you need help with Google Data Studio, you can reach me directly using the following form :
Was this post helpful?
As compared to tools like Tableau or Looker, Google Data Studio is pretty simple. However, for a lot of users, it will be their first confrontation with data manipulations like…
Here is a short article on how to freeze top rows in Google Data Studio with Tables. The fix itself might be quickly outdated but the solution is interesting as…
Transferring Google Data Studio ownership is not as straightforward as it should. Firstly it is essential to understand that transferring ownership of a report is not the same as transferring…