I'm trying to create a date filter that on the 1st of the month it shows previous month otherwise it's MTD. However, it must also allow for the date range to remain dynamic. The below code allows me to show MTD and on the 1st it will show last month. However, I lose all date range functionality. Does anyone have any suggestions or have run into this before? I'm sure there are more eloquent ways to write this but I'm more concerned about being able to keep the dynamic date ranges.
Thanks!
CASE
WHEN DAYOFMONTH(CONVERT_TZ(NOW(),'UTC','America/New_York')) > 1
AND MONTH(`TransDate`) = MONTH(CONVERT_TZ(NOW(),'UTC','America/New_York'))
AND YEAR(`TransDate`) = YEAR(CONVERT_TZ(NOW(),'UTC','America/New_York'))
THEN 1
WHEN DAYOFMONTH(CONVERT_TZ(NOW(),'UTC','America/New_York')) = 1
AND MONTH(CONVERT_TZ(NOW(),'UTC','America/New_York')) > 1
AND MONTH(`TransDate`) = MONTH(CONVERT_TZ(NOW(),'UTC','America/New_York'))-1
AND YEAR(`TransDate`) = YEAR(CONVERT_TZ(NOW(),'UTC','America/New_York'))
THEN 1
WHEN DAYOFMONTH(CONVERT_TZ(NOW(),'UTC','America/New_York')) = 1
AND MONTH(CONVERT_TZ(NOW(),'UTC','America/New_York')) = 1
AND MONTH(`TransDate`) = MONTH(CONVERT_TZ(NOW(),'UTC','America/New_York'))+11
AND YEAR(`TransDate`) = YEAR(CONVERT_TZ(NOW(),'UTC','America/New_York'))-1
THEN 1
ELSE 0
END