Current vs Previous Fiscal Year-to-Date Formulas

Michael Wilkesen

I am trying two compete two formulas that will both calculate a YTD total that compares the number of "resolved" tickets to date in the current Fiscal Year and number of tickets "resolved" the same date range of the previous year. Here is what I have, but the result of both calculations is returning "0," which perplexes me.

Formula for PREVIOUS FYTD:

COUNTIFS([Ticket Status]:[Ticket Status],"Resolved", [Date Resolved]:[Date Resolved],DATE(2020,07,01)<=TODAY()-365)

Formula for CURRENT FYTD:

COUNTIFS([Ticket Status]:[Ticket Status],"Resolved", [Date Resolved]:[Date Resolved],DATE(2020,07,01)<=TODAY())

• (2020,07,01) represents the first day of the Fiscal Year

Feedback is GREAT appreciated!

Mike Raposo

Hi @Michael Wilkesen

I've tweaked your formulas below and believe they should now work as intended. The issue was in the syntax towards the end of the formula. Instead of using "DATE(2020,07,01)<=TODAY()-365)" as the criteria you can just use "@cell <= DATE(2020, 07, 01)", however for the previous year you need to add another condition so it's only counting dates between 07/01/19 and 07/01/20

Previous FYTD:

=COUNTIFS([Ticket Status]:[Ticket Status], ="Resolved", [Date Resolved]:[Date Resolved], @cell <= DATE(2020, 7, 1), [Date Resolved]:[Date Resolved], @cell > DATE(2019, 7, 1))

Current FYTD:

=COUNTIFS([Ticket Status]:[Ticket Status], ="Resolved", [Date Resolved]:[Date Resolved], @cell >= DATE(2020, 7, 1))

Hope this helps! Feel free to let me know if you have any questios.

Best,

Mike

Michael Wilkesen

@Mike Raposo , thank you for the feedback. your "Current FYTD" worked like a charm, the "Previous FYTD" didn't quite work, but gave me the insight to see where I needed to go with it; and that is probably because I wasn't completely clear in my description.

Here is what I ended with:

=COUNTIFS([Ticket Status]:[Ticket Status], ="Resolved", [Date Resolved]:[Date Resolved], @cell > DATE(2019, 7, 1),[Date Resolved]:[Date Resolved], @cell<TODAY()-365)

This allowed me to see how many tickets were "Resolved" between the first day of the pervious FY (2019,7,1) through the "same date as today" of the previous year (TODAY-365).

Much appreciated!