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 MAX(   Date  ) 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 join key

  • 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)”)

  • And use   Date   as Date Range


Data source 2



  • Select the same data source put   Date   as join key

  • Select the same metric and rename it in order to distinguish it from the first one. In our case I renamed it to   Followers (LD)  , so we know these are the followers corresponding to the latest available date

  • Leave Date Range empty

  • Text Hover

Latest value scorecard configuration


Now we just need to add the scorecard to the dashboard and use   Followers (LD)   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.