Dynamic filtering inside custom timeframe

The challenge is with this formula: reservations based on the event date year-to-date. So, how much reservation has been made this year, for this year - compared to the same in 2023.

So, for the year 2024, it's easy; we'll set a dynamic filter "reservation date is current year-to-date" and the event date as 2024.
However, 2023 poses a more challenges. Because constructing the reservation date as a dynamic filter for the previous year (current year-to-date -1 year), has been very hard. Does anyone know how to do this properly, or this is only achievable with specific dates?

Ideally, you'd create a date dimension to handle that.

If you can't extend the database, what database are you using and what have you tried so far?

Does this help:

Thanks for the input! :slight_smile:

By creating a date dimension do you mean setting up a custom column? Like for example "year 2023" custom column where we would reference the expression?

How would the query look like with your idea?

No. A date dimension is another table/view that just contains dates and more information about each date - quarter, year etc. It's a standard thing in dimensional modelling.

Okay, I cannot extend the database like that unfortunately. We just have these two date fields to play around. I tried different formulas but I run into syntax error all the time so I was thinking if my expression is even possible at all. I use the metabases own query builder.

You need to provide some information. Database, existing attempt etc