# Summing Up Last 12 Weeks Beast Mode

Hi There,

I am trying to do write a beast mode to sum up Revenue from the Last 12 weeks but not counting the previous week. for example

Last Week = 11/22 - 11/28 (Sunday - Saturday)

I want to go back 12 weeks from last week, so I would start from the Week of 11/15

This is what I've come up with but it doesn't seem to return the correct value

SUM(CASE WHEN (`Date`) > DATE_SUB(LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK)), INTERVAL 14 WEEK)
AND (`Date`) <=
LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK))
THEN `Revenue`

END)

Thanks!

## Best Answers

• Indiana 🥷
Answer ✓

How is the returned value not correct? Is it too high? Too low?

Looking at your query it appears that you're using the LAST_DAY function which returns the last day of the month and not the last day of the week.

Try something like this:

```CASE WHEN `dt` >= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (7*12))
AND `dt` <= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (0*7))
THEN 1
ELSE 0
END ```

This ENDs at the end of last week and starts 12 weeks before the prior week. If necessary you can increase your multiplication factors by 1 to move back a week so it'd be 13-2 instead of 12-1

**Was this post helpful? Click Agree or Like below**

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

Thanks, Grant!

Wasn't aware that Last Day was for month - I used that beast mode for a "Last 12 months" and thought I could just easily modify with "Weeks" ? good to know

So I tried out your query (shifted to 13-2) and the value that's being returned is slightly higher than what's being returned via the query. I attached a screengrab but when I am manually summing it up in excel, I get \$20,009. Through the beast mode, I am getting \$20,148. For some reason it's including the value from the single day of August 29th, 2020. I can't seem to figure out why it's doing that....

• Indiana 🥷
Answer ✓

Minor tweak needed for the beast mode (just need to use > instead of >= - sorry!)

```SUM(CASE WHEN `Date` > DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (13*7))
AND `Date` <= DATE_SUB(CURRENT_DATE(), DAYOFWEEK(CURRENT_DATE()) + (1*7))
THEN `Revenue`
ELSE 0
END)```

**Was this post helpful? Click Agree or Like below**

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

## Answers

• That makes sense now - so we want to look at the date after not the "day". it adds up now. thanks so much!!