AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
2.4K members online now
2.4K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply
Highlighted

Selecting Metric for Last Date Specified and Not summing the numbers

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I am currently bringing in Data for the increments over time and looking to have a metric that just takes the very last date specified in the date range. 

 

An example would be Facebook Page likes, we bring in the data by month by day. For a monthly report, we want to just take the last number that has the total likes as their data is exported like

 

Day   Total Likes

1         25

2         29

3         31

4         37

and so on.

 

So I would want 37 to be displayed and not a sum of all the numbers or if I select a date range of 1-2 I want the last date which is 2 data to display which would be 29 and not 54 when it sums it up.

 

Max could work but there might be times where the number decreases.

 

I think maybe a customer case statement for last days of every month might work but would prefer to have an option for just the last date selected. If there is an option for this it would be extremely helpful for multiple projects

Selecting Metric for Last Date Specified and Not summing the numbers

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

I'm struggling with a similar issue. Currently the MAX function doesn't work with date fields, so it's impossible to find the last date in a date range.

Selecting Metric for Last Date Specified and Not summing the numbers

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Would a case statement work be a potential answer in some way even for just last day of the months and do not give the option of changing the date?

Selecting Metric for Last Date Specified and Not summing the numbers

Explorer ✭ ✭ ✭
# 4
Explorer ✭ ✭ ✭

What would your case statement look like?

Selecting Metric for Last Date Specified and Not summing the numbers

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

something like

 

CASE

WHEN Date = 2018-01-31 THEN Total likes ELSE 0

CLOSE

 

and do an In statement for all last days of the month, potentially any other for months that have different dates each year but along that line

Selecting Metric for Last Date Specified and Not summing the numbers

Explorer ✭ ✭ ✭
# 6
Explorer ✭ ✭ ✭

Did that CASE function work Michael?

 

I can't figure out the format the date comparison needs to be in for it to work.

Selecting Metric for Last Date Specified and Not summing the numbers

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

no, I never got it to work, I feel like it should but sadly no,

 

but ideally, I'd love to set it just select the last date specified in the date range