TIP: Metabase date range filters with default values and used with multiple tables

Quick tip here (not asking for help).

We've been migrating from Chartio/Redash to Metabase this last month, and pretty much ALL our dashboards have a filter for date range, where you need to select a start date and an end date. We have some overview reports that query multiple tables with different columns and the filters have to be relative and work different tables.

Problem is that Metabase filters are super limited. For some reason they decided to have separate filters for things like relative date and also tie it to database columns (so no way to use two different columns on the same filter). Since no other BI tool that we've used before did this we had a tough time migrating with the same UX.

It took a while for us to find a solution for this design issue, so I figured we should share here to help others in the same situation. You can basically use two variables and set a default for the start date only, use a optional variable [[]] that falls back to the current date. Like this:

created_at BETWEEN {{ period_start }} AND [[ {{ period_end }} -- ]] CURRENT_DATE

I hope this helps.

Just for reference: https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#setting-complex-default-values-in-the-query

1 Like