YTD vs Year Prior

zuchu · 2024-04-18T16:13:14+00:00

There was an error rendering this rich post.

Hi guys,

Looked in other posts here but couldn't find the exact answer. I'm creating a table card that would show YTD vs Year prior (Bar charts are not an option since comparing many different categories. The best I could find to show Year prior is

SUM(CASE WHEN YEAR(DocDate)= YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND month(DocDate) <= month(CURDATE()) THEN DocTotal_Contribution ELSE 0 END)

The issue with that is that is is summarizing all data from 2023 including all month of April. I want to compare data from time Jan 01 2023 till today (April 18 of 2023). Vs Year to Date (Jan 01 2024 vs today)

To calculate YTD I use the following formula and is working just fine.

SUM(CASE WHEN YEAR(DocDate) = YEAR(CURDATE()) THEN DocTotal_Contribution ELSE 0 END)

How can I add the extra layer to count also the current day in the year prior?

Thanks

Find more posts tagged with

Accepted answers

All comments

ArborRose

That's the formula I use. The version you show is not summing the full year. That month condition means it is summing YTD (year to date)…..less than or equal to the current month. You need to put some more criteria to it for the period you want. I am about to go to a meeting, I'll try to give you something specific when I can.

ArborRose

The condition "<= month(CURDATE())" means compare years up until today's date. It is comparing Jan through today (April 18, 2024). For specific ranges like a specific month:

To include records where the month is April -

SUM(CASE WHEN YEAR(DocDate) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))
AND MONTH(DocDate) = 4
THEN DocTotal_Contribution
ELSE 0 END)

ArborRose

Oh…make it less than but not equal if you want up to today but not including today.

zuchu

@ArborRose thank for your help. I tried your syntaxes but they all seem to be including full April for 2023.

This syntax

SUM(CASE WHEN YEAR(DocDate)= YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)) AND month(DocDate) <= MONTH(CURDATE()) THen DocTotal_Contribution ELSE 0 END)

still returns data for 2023 including full month of April. I think the logic in the syntax above includes all data for year prior PRIOR to the end of April 2023. Any way to change this logic to show only until 18? It will be a live card so I don't want to use specific numbers so it the card the update with time.

zuchu

@ArborRose this is the formula I just used and it returns what I need

SUM(CASE WHEN DocDate <= DATE_SUB(CURDATE(),INTERVAL 1 YEAR) AND DocDate >= DATE_FORMAT(DATE_SUB(CURDATE(),INTERVAL 1 YEAR), '%Y-01-01')
THEN DocTotal_Contribution END)

Thanks