# Need help with beast modes to show yesterday's invoice total amount, and same day last year

edited November 9

I'm trying to create 2 beast modes to show yesterday's Total Invoice Amount and same day last year's Total Invoice Amount. For example, 10/27/2022, and 10/28/21

Yesterday: case when year(`INVOICE_DATE`) = year(CURDATE()) and MONTH(`INVOICE_DATE`) = MONTH(CURDATE()) then `INVOICE_TOTAL_AMOUNT` end --- This formula works

Same day last year: case when year(`INVOICE_DATE`)=YEAR(CURDATE()) -1 and YEAR(INVOICE_DATE) = MONTH(CURDATE()) then `INVOICE_TOTAL_AMOUNT`

Same day last year's formula/code does not work.

Tagged:

• Watch out for your second portion of the evaluation...easy typo but you're looking at YEAR() vs MONTH() so it's not going to yield any results.

Same day last year: case when year(`INVOICE_DATE`)=YEAR(CURDATE()) -1 and YEAR(INVOICE_DATE) = MONTH(CURDATE()) then `INVOICE_TOTAL_AMOUNT`

^^^ instead of year(invoice_date) I think you'd want month(invoice_date)

Your formulas don't really sync up with how you're describing the need. The "yesterday" beast shown is more like "current month to-date, including today". Yesterday would be something along the lines of case when INVOICE_DATE=CURDATE()-1 then INVOICE_TOTAL_AMOUNT.

When talking about yesterday year over year you can do it many ways. Keep leap years in mind so you're ready for 2024. Using DAYOFYEAR() is really easy and will work fine till 2024, then it's not a perfect calendar day match for most months. Or could use something along the lines of WHEN DATE_SUB(CURDATE(),INTERVAL 366 DAY) = INVOICE_DATE THEN INVOICE_TOTAL_AMOUNT. Or something that looks at DAY/MONTH/YEAR-1 matches. So many ways... :)

• Hi Bilobi, thank you for your comment. Let me explain again so you can understand better. My grammar is bad. Sorry. What I'm trying to do is I'm creating a daily ecommerce report, that shows previous day's report vs same day previous year's report. This sentence is really confusing. For example, yesterday Thursday was 10/27/22 and last year was Thursday 10/28/21 (+1 day). Because I selected the date range as "Yesterday" (see top right corner), below picture is showing invoice total amount for yesterday. What i want to do is i want to create a beast mode that can show invoice total amount same day last year (which is 10/28/21), how can i do that? Please help

Formula for yesterday Invoice total amount is case when year(`INVOICE_DATE`) = year(CURDATE()) and MONTH(`INVOICE_DATE`) = MONTH(CURDATE()) then `INVOICE_TOTAL_AMOUNT`end

As you can see, this yesterday beast mode matches the invoice_total_amount ( I used InvoiceTotal_Amount as Measures and I sum it. The numbers match

Formula for same day last year Total:

case when YEAR(`INVOICE_DATE`) =YEAR(CURDATE()) -1 and MONTH(`INVOICE_DATE`) = MONTH(CURDATE()) then `INVOICE_TOTAL_AMOUNT` END

Same day last year is not showing any numbers. I don't know what to do.