Date search formula for last month and last year’s last month

To check if the transaction is in the last month dynamically based on today’s date

CASE WHEN TO_DATE({trandate}, ‘DD-MM-YYYY’) between  TO_DATE(ADD_MONTHS(TRUNC({today}, ‘MM’), -1),’DD-MM-YYYY’)  AND TO_DATE(’01-‘ || TO_CHAR({today}, ‘MM-YYYY’), ‘DD-MM-YYYY’) – 1 THEN (CASE WHEN {accounttype} = ‘Income’ THEN {amount} ELSE 0 END) ELSE 0 END

To check if the transaction is in the last month of last year dynamically based on today’s date

CASE WHEN TO_DATE({trandate}, ‘DD-MM-YYYY’) between TO_DATE(’01-‘ || TO_CHAR(ADD_MONTHS({today}, -13), ‘MM-YYYY’), ‘DD-MM-YYYY’) AND TO_DATE(’01-‘ || TO_CHAR(ADD_MONTHS({today}, -12), ‘MM-YYYY’), ‘DD-MM-YYYY’) – 1 THEN (CASE WHEN {accounttype} = ‘Income’ THEN {amount} ELSE 0 END) ELSE 0 END