# Beast Mode Filter

Options
Member

Hello All,

I have these 2 Beast Mode calc and I am trying to understand why it is not filtering from.

(2022-01-01) to (today).

What am I missing here please?

And with this below too why am I getting error. Any help please?

I just want to filter dates from start of previous year to today of last year.

2022-01-01 to same day today of last (2022-11-06)

thank you all.

• Coach
Options

@carthur On your second formula, you are getting an error because the logic needs to be within a case statement. I'd suggest doing this instead:

```case
when YEAR(`ORDER_DATE`) = YEAR(DATE_SUB(CURDATE(),interval 1 year))
and ORDER_DATE <= DATE_SUB(CURDATE(),interval 1 year)
then 'Include'
else 'Exclude'
end
```

• Coach
Options
```CURRENT_DATE() - INTERVAL (DAYOFYEAR(`ORDER_DATE`)-1) DAY - INTERVAL 1 YEAR
```
• DAYOFYEAR - Returns the day number of the year of the given date, ORDER_DATE in your example. (Jan 1 = 1, Dec 31 = 365/366)
• - INTERVAL (DAYOFYEAR(`ORDER_DATE`)-1) DAY - Subtract the number of days the current date is minus one so we get to day 1 of the year (Jan 1st)
• - INTERVAL 1 YEAR - Subtract an entire year so you'll get January 1st of the prior year

-INTERVAL is the same as DATE_SUB just a different format.

**Did this solve your problem? Accept it as a solution!**

• Coach
edited November 2023
Options

Your STR_TO_DATE format is not quite correct.

The first one isn't including the dashes that you've concatenated in there. The second is including commans which aren't included.

Try something like this:

```CASE WHEN `ORDER_DATE` >= STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE()-1), '-01-01'), '%Y-%m-%d')
AND
`ORDER_DATE` <= STR_TO_DATE(CONCAT(YEAR(CURRENT_DATE())-1, '-', MONTH(CURRENT_DATE()), '-', DAY(CURRENT_DATE())), '%Y-%m-%d')
THEN 'YES'
ELSE 'NO'
END
```

**Did this solve your problem? Accept it as a solution!**
• Coach
edited November 2023
Options

You can also simplify the conditions to:

```CASE WHEN `ORDER_DATE` >= CURRENT_DATE() - INTERVAL (DAYOFYEAR(`ORDER_DATE`)-1) DAY - INTERVAL 1 YEAR
AND
`ORDER_DATE` <= CURRENT_DATE() - INTERVAL 1 YEAR
THEN 'YES'
ELSE 'NO'
END
```

**Did this solve your problem? Accept it as a solution!**
• Coach
Options

@carthur On your second formula, you are getting an error because the logic needs to be within a case statement. I'd suggest doing this instead:

```case
when YEAR(`ORDER_DATE`) = YEAR(DATE_SUB(CURDATE(),interval 1 year))
and ORDER_DATE <= DATE_SUB(CURDATE(),interval 1 year)
then 'Include'
else 'Exclude'
end
```

• Member
Options

Thank you both.

@GrantSmith your second option didn't work but very interesting calculation, would you please explain that concept a little for me?

• Coach
```CURRENT_DATE() - INTERVAL (DAYOFYEAR(`ORDER_DATE`)-1) DAY - INTERVAL 1 YEAR