Data Studio, Data visualisation
How to show the latest value of a metric in Data Studio
When our raw data is cumulative, we have to show the latest or most recent value we have available for that particular metric. Summing up all values for the period would simply give us a wrong number.
Assume we have a spreadsheet of Facebook followers per day and want to have a scorecard with the latest number of followers in Data Studio.
Now, Data Studio doesn’t give us the option to choose the latest or most recent available value. Of course, there are a number of workarounds:
- We could create a metric MAX( followers ) which would give us some value close enough to the real number, except if we have been losing followers.
- We could set the date of the scorecard to yesterday, something that would not work though if our table isn’t always up to date
- We could use a table, sort it by date, show the latest value at the top and then adjust the layout, but it doesn’t look polished enough
The solution I’m going to propose here is going to rely on a custom date field and blended data. Let’s look at it.
The result of the setup
As you can see in the below image, the scorecard will work as a normal non-acumumlated metric, which responds even to the date control selector.
The setup
For example, we have here a Facebook data export with the cumulative number of followers, as you can see in the image below.
The total number of followers keeps on adding up – once we try and visualise this data in a scorecard in Data Studio, the rult will be wrong. In fact, depending on the aggregation type, we can get simply get the sum, average max, etc. Non of these will work.
For this purpose, we are going to create a new field and blend data
- Create a Max date field that will give us the latest day we have filled in our data source (Google Sheet in this case)
- Blend the data on itself to obtain the value corresponding to this date
Custom date field
As explained, let’s create the new date field that will allow us to get the latest available value for our metric. In the original data source
- Click on + Add field
- Paste the formula TODATE(MAX( Date ), ‘%Y-%m-%d’) and call the field “Max date”
- Save and go back to the fields list
- Change the Type our new field Max date to Date
Blended data configuration
Now let’s create the blended data source that we will use in our scorecard:
Data source 1
- Select your data source as first source and put Max date as dimension
- And use Date as Date Range
Data source 2
- Select the same data source put Date as a dimension
- Then your desired metric (in our case Followers , which when you extract data from Facebook Insights comes as “Lifetime: The total number of people who have liked your Page. (Unique Users)”)
- Leave Date Range empty
Join configuration
- In the Blend Data configuration, click on “Configure join”
- Make sure “Left outer” is selected as “Join operator”
- In “Join conditions”, select Max date in the firts field and Date in the second
- Click “Save”
Latest value scorecard configuration
Now we just need to add the scorecard to the dashboard and use Followers as metric.
If we leave the Default date range to Auto we will then be able to add a date range controller to the dashboard. The only issue here is that if the user selects date ranges for which we don’t have any data (e.g. next month) the graph will still show the last available value.
One option we have here is then to select periods from the graph configuration – if we now we are going to update the graph monthly, we can select last month as a fixed date range, if we know we are going to update it weekly, we can select “last week”, and so on.
Choose how to show the latest value
As explained, the setup shown in ths post works to visualise the most recent available data input you have in your data set but, finally, how to visualise it – monthly, weekly or with a date control selector (maybe using a tootip to let the user know the update frequency) – finally depends on you.
Originally published on 26 July 2021 and updated following Data Studio changes that didn’t allow to use Max(DATE) as a date join key in blended data and the Join types.
8 Comments
Sophie
Hi Daniel,
This is such a nice article. It makes complete sense, that this is the way it should be done and the method is way better than everything else I’ve found online in my search. However, I would like to implement this for more fine-grained timestamps such as %d-%m-%Y H%:%M:%s. Do you have any idea whether this would be possible?
Thanks,
Sophie
Joshua
Wow! Thank you for this. I have been able to implement it in my report. I am so excited!!
hanif
Thanks you for this, you help me a lot
Karl
Hi Daniel
Great article, I was very hopeful as this is exactly what I need. It didn’t work for me though. For some reason the Max date ended up in the dimensions section. I’m not sure if this is the issue.
Maybe my data isn’t in the required format. Shame 🙁
Daniel (Author)
Thanks, Karl. It should still work as you need to use Max(date) as a dimension join key. have you tried?
Daniel (Author)
Thanks, Sophie. It should still be possible, maybe it would need some date formatting first. I’d give it a go first and then move on from there.
Debbie Wolmarans
Thanks so much for this – really helpful. I want to ask if you perhaps have a solution to display the following in the same table: e.g. A retailer has sales and stock units per day in a table. You want to display the sales over a selected group of days (in ohter words the sum of the sales) and then for stock you only want to display the closing stock (in other words the stock of the max week)
Jared
Daniel, in my dashboard I’ve created what you’ve done above as well as created one for the day before the max date in another blend. Now, I’d like to compare them to get a day over day change, but I found out you can’t write a formula on the blends. So, how would you do a day over day change using the “value on the max date in the range” functionality you go through above?