How can I pre-filter or limit report data to just include the latest report date set?

user_3383907 0
Viewed 97

I've got data in a Google Sheet. There are X # of rows for specific report dates. As more data is dumped into the sheet, X # of rows are added and tagged with that report date.

This is a sample of the source data in a Google Sheet:

Report Date ID ...
2022-01-01 1 ...
2022-01-01 2 ...
2022-01-01 3 ...
2022-02-01 1 ...
2022-02-01 2 ...
2022-02-01 3 ...
2022-01-15 1 ...
2022-01-15 2 ...
2022-01-15 3 ...
... ... ...
2021-01-01 1 ...
2021-01-01 2 ...
2021-01-01 3 ...

I have this added as a data source to Data Studio and created a table with the data like so:

enter image description here

I want to have the report automatically shows only the data for the last/latest report date set. So in the above example it would be 2022-02-01:

Report Date ID ...
2022-02-01 1 ...
2022-02-01 2 ...
2022-02-01 3 ...

More data would be added to the sheet, so the logic in Data Studio has to be dynamic.

Sample data:

google-data-studio google-sheets
1 Answers

It can be achieved with a blend and creating a MAX(Date) field (a date field aggregated by MAX) and using it as a metric and join condition so that only the latest date and respective data are displayed:

1) Blend Fields

Table 1 Table 2
Dimension 1: Report Date
Dimension 2: ID
Metric 1
Name: Report Date
Formula: MAX(Report Date)
Date Range: Report Date (Auto)
blend_table_! blend_image_2

2) Join Configuration

Join Description Table 1 Table 2
Join Operator Left Outer
Join Condition Report Date (Table 1) Report Date (Table 2)
Image join_config

In summary the blend will look like:

blend_summary

Publicly editable Google Data Studio report (embedded Google Sheets data source) and a GIF to elaborate:

gif

user_128925530
Related