Show Last value when Filter Dates

[Lina9028]

Hi !

So i'm creating a report for social media.
i put a date Filter there.

The thing is that I have a column with number of followers. When the person see the report, i need that show the last value of the data filter. For example:

I that person pick the dates from January 1 to January 15, I need need that the report show the value of followers on January 15., If the person select from January 1 to January 31 in the filter, that show the value iin the row of January 31.

How can i do that?

Thanks for your help !

[Looker_Studio_B]

Hi,

You can self blend your data source to itself with INNER JOIN. Left source: use date as a dimension. Right source: use MAX(date) as dimension, and join on these. This way, anytime you make a date selection, it will filter on the last date used

[Peibol]

Hello,

Interesting idea. I'm having same issue but when trying your approach on the blend of the data sources I get an error when using MAX(date) on the new calculated field to be used as comparative dimension. As it is an aggregated field I cannot use it as it says it's not allowed.

Any other idea?

Thanks a lot!

[Looker_Studio_B]

In your blend, the MAX(Date) should be a dimension and not a metric. It will show as red, but still work. Can you try again this way? Thanks

[Lina9028 · Accepted solution]

Hi,

Ok so I find a solution from Daniel barranger. Here are the steps:

1. Create custom date field:

1. Click on + Add field
2. Paste the formula TODATE(MAX(   Date  ), ‘%Y-%m-%d’) and call the field “Max date”
    
   
   
3. Save and go back to the fields list
4. Change the Type our new field  "Max date"  to Date
   
   
   

2. Blend data

1. Select same data source for blend
2. In te first source put   "Max date"   as dimension and use   "Date"   as Date Range
3. In the second source put   "Date"   as a dimension, then the desired metric (in this case   "Followers"  
   
4. For the  "Join configuration" select  "Left outer" as Join operator.  In “Join conditions”, select   "Max date"   in the firts field and   "Date"   in the second.
   
5. Click "Save"
   
   

 3. Create the scorecard: Add the scorecard to the dashboard and use   Followers   as metric.

Here you will find the full explination
https://danielbarranger.com/blog/show-latest-value-of-metric-in-data-studio/

[attwoliver]

The issue with this self blend technique is that now we can no longer get comparison data to compare to previous year for example. It feels like this is such a hacky way to do something that should be quite simple really! I wish this was a real feature. 

[attwoliver]

Hi, please go like this post if you think there should be a propper feature for this.

[matitowers]

Hi, I'm a little late here but today I faced the same issue that OP. I managed to do a simpler workaround for that. If you are using Bigquery as your data source, just change your connection to a custom SQL query and add a column like this one

SELECT
*,
CASE WHEN data_date = PARSE_DATE("%Y%m%d", @ds_END_DATE) THEN 1 ELSE 0 END AS is_max_selected_date
FROM `your_table`

with this you would be able to precalculate that "is max selected date" column and use it as a filter in your chart.

Hope that helped!