1st of the month with Current Month Filter

user083578

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  

   

user083578

Thank you both for such quick responses.  I wanted to test something yesterday and today before responding and it held up nicely.  Below is the code that works for me.  On the 1st it includes all of last month, then on the 2nd it moves to all of current month.  Anything prior is then captured by month-year.  Although you lose the date range filter capabilities, this still provides the option to select whichever month they want to look at.

 

Note: I used '00-Current Month' so it sits at the top of the quick filter list (see pic attached).

 

CASE 
    WHEN 
             DAY(CURRENT_DATE()) = 1 
         AND DATE_FORMAT(`TransDate`,'%m-%Y') = DATE_FORMAT(CURRENT_DATE()-1,'%m-%Y')  
     THEN '00-Current Month'

    WHEN 
             DAY(CURRENT_DATE()) != 1 
         AND DATE_FORMAT(`TransDate`,'%m-%Y') = DATE_FORMAT(CURRENT_DATE(),'%m-%Y')  
     THEN '00-Current Month'

    ELSE DATE_FORMAT(`TransDate`,'%m-%Y')
END

 

DateFilterPNG.PNG