How to set date range for comparing this year and last year (to date)

I need to compare sum of sales of this year (1 january 2022 - today) VS last year performance to same date (1 january 2021 - today date , year is 2021).
I have tried to use filter but not find this kind of filtering.
My plan is to create custom expression for 2021 sales by using sumif ([sales], between [date] ,"2021-1-1", "2021- this month - today date) but unfortunately I dont know how to set the limit date to today date and this month with year is 2021.

Are there any way for like this in Metabase? if not possible using sumif, please advise another way to do it. Is it possible make custom colomn by date calculation like today-365 ?

Thank you

Best Regards

Hi @taufik
With a bit more complex expression, then you should be able to do it, example:

# This year
SumIf([sales], between([date], relativeDateTime(0, "year"), relativeDateTime(0, "day")))
# Previous year
SumIf([sales], between([date], relativeDateTime(-1, "year"), relativeDateTime(-365, "day")))

Hi Flamber
Thank you very much for your prompt reply. I'm very happy your answer really match my expectation. Metabase is a really powerful BI. I love it!

Thank you
Best Regards

Hi flamber, coming from this one it seems the function here is not working for me.

SumIf([sales], between([date], relativeDateTime(0, "year"), relativeDateTime(0, "day")))

I wonder where my mistake is?

Screenshot 1 all good
Screenshot 2 (set comma for second argument for between function - dead)
Screenshot 3: Full expression

See my screenshot attached here

@germanprinter If you are not using the latest release, then upgrade:
Otherwise you have to follow