I'm building a dashboard in Looker Studio connected to a Google Sheets data source.
In my sheet, each record is assigned to a specific month, and the date field is standardized — for example: 01/05/2025 represents the entire month of May 2025 (I don’t have day-level granularity).
What I’m trying to achieve:
I want users to select any date or date range within a given month (e.g. May 7–13), and the dashboard should dynamically show all records associated with that entire month, even if the underlying record only has 01/05/2025 as its date.
What I tried:
Created a "month/year" field with values like 01/05/2025
Used a "Date" field selected via the Date Range filter in the dashboard
Added a calculated field: FORMAT_DATETIME("%Y-%m", Date) = FORMAT_DATETIME("%Y-%m", month/year)
Created a flag field ("is_same_month") to return "view" or "hide"
Applied a filter to only include records where is_same_month = "view"
What’s not working:
When users select a date range that doesn’t include the 1st of the month (e.g. May 7–13), no records are shown — because all my records only have 01/05/2025 as their date, and it’s outside the selected range.
What I’m looking for:
Is there a way to dynamically match the selected date (from the Date Range filter) to the month of each record, so that any date selected within a month triggers all data for that month?
Or is there a way to normalize or extract the month from the selected date in the filter to compare it more effectively?
Thanks in advance for any help.
I like your idea of creating a calculated field that extracts the month/year. Could you have your users filter on that calculated field rather than the date field itself?
User | Count |
---|---|
3 | |
3 | |
1 | |
1 | |
1 |