MTD and MTD Last Year, leap year problem

verytiredgirl

I want to show sales MTD and MTD Last Year as of Feb 28, 2025 so I created Beast Mode:

MTD = CASE 
      WHEN CURRENT_DATE >= SHIP_DATE and CURRENT_DATE <= LASTDAY(SHIP_DATE) 
      THEN 'sales_amount'  
      ELSE 0 
  END 

MTD Last Year 
= CASE 
  WHEN DATE_ADD(CURRENT_DATE, INTERVAL -1 YEAR) >= SHIP_DATE 
  AND DATE_ADD(CURRENT_DATE, INTERVAL -1 YEAR) <= LASTDAY(SHIP_DATE) 
  THEN 'sales_amount' 
  ELSE 0 
END 

If my CURRENT_DATE is Feb 28, 2024. MTD would show me sales from Feb 1 - 28, 2025, which is correct. MTD Last Year shows me sales from Feb 1 - 28, 2024 but I need it to show me Feb 1- 29, 2024.

So ideally, if the Current Date is the last day of the month, MTD and MTD Last Year should show sales for that entire month of the Current Date

Manasi_Panov

Hello @verytiredgirl,

You can try with nested CASE statements and first check if the current day is the last day of the month:

CASE

WHEN CURRENT_DATE() = LAST_DAY(CURRENT_DATE()) THEN

CASE

WHEN MONTH(CURRENT_DATE()) = MONTH(SHIP_DATE) AND YEAR(CURRENT_DATE()) = YEAR(SHIP_DATE)

THEN 'sales_amount'

ELSE 0

END

ELSE [your CASE statement]

For the MTD Last year will be something like this:

CASE

WHEN CURRENT_DATE() = LAST_DAY(CURRENT_DATE()) THEN

CASE

WHEN MONTH(CURRENT_DATE()) = MONTH(SHIP_DATE) AND YEAR(CURRENT_DATE()) - 1 = YEAR(SHIP_DATE)

THEN 'sales_amount'

ELSE 0

END

ELSE [your CASE statement]