I'm working on replacing a custom-build SaaS dashboard with Metabase and am unsure the best way to handle some date filter behavior.
Current state: Our app stores a subscription history table using Slowly Changing Dimension Type 2. Then we have a view that expands this with a date column so there is a row for every subscription for every date. This way, for a given date, we can easily query the entire state of all subscriptions. The current dashboard lets the user input a Start Date and End Date. For selecting a metric like "New Subscriptions" for last month we can do:
SELECT count(distinct id)
FROM subscription_history_expanded
WHERE
`status` = "Paid"
AND `created_at` >= '2025-01-01' -- Start Date
AND `effective_date` = '2025-01-31' -- End Date
When trying to build this out as a Dashboard, I'm unsure the best way to handle this:
- Creating this as a GUI question doesn't seem possible, I can add the Start Date & End date fields, but linking them to the question always make it an "equals" comparison, I can't do "greater than equal to" on a filter value
- Creating it as SQL query works, but then I lose the ability to drill-through to see the individual records (which is a key feature I was looking for).
- I can change the Click behavior to go to the underlying model and pass in the filters, but I can't specify the comparison, so it passes the created at as
=and I need>=
- I can change the Click behavior to go to the underlying model and pass in the filters, but I can't specify the comparison, so it passes the created at as
Am I missing anything or what is the best approach here?