I need to build out a forcasting dashboard for my purchasing team. They need to look at the last 3 months sales for some items and I was able to get a filter to give me that range in my ETL. However, the other metric they use for some items is a term called "forward 3 months". This means that they need the next three months but from last year. For example, if the current month is September 2023, the forward 3 months would be Oct. - Dec. 2022. I've tried several formulas but all have failed to produce any rows in the test feed I built directly off that filter.

Here's the current formula that I thought would be close but is not working.

(year(Date)=DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND month(Date)>=DATE_add(CURDATE(), INTERVAL 1 MONTH))

AND
(year(Date)=DATE_SUB(CURDATE(), INTERVAL 1 YEAR) AND month(Date)<=DATE_add(CURDATE(), INTERVAL 3 MONTH))