Need help with beast modes to show yesterday's invoice total amount, and previous year, current date

anh_tr_huynh4

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 formular/code does not work.

Please help me. Thank you so much

DashboardDude

Hi @anh_tr_huynh4 . Here's a video explanation of what you need to do: https://www.loom.com/share/70b71aee2ec44253bb4638c9862f50cb

Anyway, you're on the right track, but I don't think your formulas are doing what you want them to do. By using the year and month functions, you're really saying to get a sum of invoice amounts for an entire month and not just yesterday.

Hope the video helps

John Le

Dashboarddudes.com

DavidChurchman

Looks like you're missing "END" in the second formula

DashboardDude

Hi @anh_tr_huynh4 . Here's a video explanation of what you need to do: https://www.loom.com/share/70b71aee2ec44253bb4638c9862f50cb

Anyway, you're on the right track, but I don't think your formulas are doing what you want them to do. By using the year and month functions, you're really saying to get a sum of invoice amounts for an entire month and not just yesterday.

Hope the video helps

John Le

Dashboarddudes.com

anh_tr_huynh4

@DashboardDude I followed your guidance and the formulas worked. Thank you for your help. Cheers