I’m building a report in Looker Studio where I have a date range control to filter data. My goal is to configure a pie chart that always shows data for the latest available date within the selected range in the date control.
Example:
I have data available from 10/11/2024 to 20/11/2024.
If I select the date range from 10/11/2024 to 15/11/2024 in the date control, I want the chart to display only the data for 15/11/2024 (the latest available date within the selected range).
If I select the date range from 10/11/2024 to 20/11/2024, it should display data for 20/11/2024.
Issues I’m facing:
I tried creating a SQL query in BigQuery to always fetch the latest date (MAX(dataAtualizacao)), but this ignores the date range selected in Looker Studio.
I couldn’t find a way to create a dynamic calculated field or filter in Looker Studio that automatically adjusts to the latest available date based on the selected range in the date control.
Question:
Is there a way to configure a pie chart in Looker Studio so that it only displays data for the latest available date, respecting the selected date range in the date control?
Context:
I’m building an investment dashboard, and the pie chart is essential for showing the percentage of each asset class on a specific date. However, since Looker Studio automatically aggregates data when setting metrics, the chart ends up summing values that don’t represent the actual distribution.
The data is stored in BigQuery.