How to filter a data range dynamically by default in Google Data Studio?

Question

I have a table with a date column. There are 2 alternative dynamic filters I'd like to have by default in the table I prepared inside Google Data Studio:

1. Show only data from the latest date in the database.
2. Show only data from yesterday (as in today-1). I like this approach less because it assumes the database table will always have yesterday.

If the sample data is:

Date        Name
2020-01-03  Joe
2020-01-02  Steve

Then 1. will return only the row of Joe, while 2. will return nothing since there's no yesterday.

Had Joe's date been yesterday, with Steve's date unchanged, then both 1. and 2. would have returned only the row of Joe.

So how can I do any of these dynamic filters? Note I don't need them at the same time, those are just alternative approaches.

Answer 1

Turns out the second approach can be done by adding a Date Range Dimension, which then allows to change the Default date range:

I don't think there's anything in the UI to achieve the first approach directly.

However, indirectly in the original data source (maybe through CUSTOM QUERY?), you can create a column like IF(Date=MAX(Date), "Yes", "No") AS "Max date", then filter inside GDS for only "Yes" in that column.

I tried to do that as a Calculated Dimension in GDS itself without needing an external query, but it was blocked as seems like it can't use a function inside a CASE WHEN statement (e.g. CASE WHEN Date=MAX(Date) THEN "Yes") - not even CASE WHEN Date=Dummy THEN "Yes" if you create DUMMY as a secondary calculated dimension of MAX(Date).