Need help with Beast Mode for specific time period

kodonnellLT

I'm trying to create a beast mode that calculates revenue for

jan-jun and then july- dec

SUM(CASE WHEN Month(Period) = MONTH(CURDATE()) -6 AND DAYOFYEAR(Period) <= DAYOFYEAR(CURDATE()) and Item THEN a_amount END)

MichelleH

@kodonnellLT If you want to create calculations for the first 6 months an last 6 months of the year, you will only need to compare whether the month number is greater or less than 6 like below.

Jan-Jun

SUM(CASE WHEN MONTH(`Period`) <= 6 then `a_amount` else 0 end)

July-Dec

SUM(CASE WHEN MONTH(`Period`) >= 7 then `a_amount` else 0 end)

Or if you need to calculate the totals for a rolling date compared to today, then you will need to use the DATE_SUB function.

SUM(CASE WHEN LAST_DAY(`Period`) >= DATE_SUB(LAST_DAY(CURRENT_DATE()),interval 6 month) then `a_amount` else 0 end)

ColemenWilson

So do you want 2 different beastmodes? One for the first period and one for the second? If so, here is what the first period would look like:

SUM(CASE WHEN MONTH(`Period`) >= 1 AND MONTH(`Period`) <= 6 AND YEAR(`Period`) = 2023 THEN `a_amount` END)

The "AND `Item`" part of your beastmode isn't doing anything currently. What are you trying to do with it?

MichelleH

@kodonnellLT If you want to create calculations for the first 6 months an last 6 months of the year, you will only need to compare whether the month number is greater or less than 6 like below.

Jan-Jun

SUM(CASE WHEN MONTH(`Period`) <= 6 then `a_amount` else 0 end)

July-Dec

SUM(CASE WHEN MONTH(`Period`) >= 7 then `a_amount` else 0 end)

Or if you need to calculate the totals for a rolling date compared to today, then you will need to use the DATE_SUB function.

SUM(CASE WHEN LAST_DAY(`Period`) >= DATE_SUB(LAST_DAY(CURRENT_DATE()),interval 6 month) then `a_amount` else 0 end)

ColemenWilson

So do you want 2 different beastmodes? One for the first period and one for the second? If so, here is what the first period would look like:

SUM(CASE WHEN MONTH(`Period`) >= 1 AND MONTH(`Period`) <= 6 AND YEAR(`Period`) = 2023 THEN `a_amount` END)

The "AND `Item`" part of your beastmode isn't doing anything currently. What are you trying to do with it?