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.

  • Text Hover

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.

  • Text Hover


For this purpose, we are going to create a new field and blend data

  1. Create a   Max date   field that will give us the latest day we have filled in our data source (Google Sheet in this case)
  2. 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

  1. Click on + Add field
  2. Paste the formula TODATE(MAX(   Date  ), ‘%Y-%m-%d’) and call the field “Max date”
  • Text Hover

  1. Save and go back to the fields list
  2. Change the Type our new field Max date to Date

  • Text Hover

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
  • Text Hover

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”
  • Text Hover

Latest value scorecard configuration

Now we just need to add the scorecard to the dashboard and use   Followers   as metric.

  • Text Hover

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.