Using max date as filter or date range for chart

Looker Q&A Looker Studio
1

Hi, I’m working with looker studio, and I was requested to build something that I’m not sure is possible.

Here is the use case: we have money transactions data, and we have table#1 that do simple sum on all rows.
table#2 how ever, is doing sum only on the current month.

I was requested to create a control, date picker, of month-year, that will be create the ability to see the history.
So when I remove months from the selection, for table#2 it will take the max (last) month as the “current month”.
Table#2 always so transactions of one month (the latest out of the selections)

For example:
Table#1 - will present all data from all selected _billing_dates
Table#2 - will present data only from Aug 2024 → the max (last) month

Nic3Guy_0-1729363684215.png
Nic3Guy_0-1729363684215.png302×480 17.6 KB

I was not able to create this, tried many combinations, many uses of calculated fields and parameters, but couldn’t get to the solution, if there is any.
I always reach the point where I cannot do calculated field that will do simple if like:

if (_billing_date <= max(_billing_date), value, 0)

I get the error:
Invalid formula - Invalid input expression. - Aggregated and non-aggregated fields cannot be mixed in a Boolean Expression

please help :sweat_smile:

2

Cool use case! So to paraphrase, it sounds like you want table 2 to only show data from the most recent month that has data.

Like you mentioned, combining dimensions and measures in a calculated field or filter will get sticky. I wonder if there’s another way to get what you’re after?

For example, is there a field that is null for dates after August 2024? Maybe you can filter on that field being not null, sort chronologically, and then limit the rows in table 2 so that only the most recent data is visible?