How to I point to an invoiced amount but from a previous year?

FreshSqueezed ⚪️
edited January 4 in Beastmode & Analytics

We switched ERP systems mid-year last year. Now I cannot get my Previous Year Actual to populate in the current year (as a different data field). If I set my date settings to last year, our invoiced sales will load. However when I set the date field to This year, this year's invoiced sales will load, however Last Year's won't. I thought the solution would be simple, but I'm not getting results.

Here's the Formula I'm using:

Exec Date = The date of the invoice I want to reference

Invoiced Amount = The $ amount of the invoice I want to capture.

My formula: case when YEAR(`Exec date`)-1 = YEAR(`Exec date`) THEN `Invoiced Amount` else 0 END