Reporting Year-to-Date vs Last Year
A Tale of Troubleshooting, Testing, Refactoring, and Redemption
This week’s episode of Q&A with Matt K turned in to a two-parter thanks to some too-big-for-their-britches refactoring, courtesy of me :), and a last-second save from Matt — but the end result is a solution that not only addressed the question, but has already been implemented in our own org to solve a long-standing request as well! Let’s dive in!
Reporting (and charting) YTD vs the Same Period Last Year
This round’s question came via the Trailblazer Community where Tracy asked for help filtering a report:
“I would like to produce a report that shows archaic data from 2023 that runs alongside the same report but that one shows the same data for 2024.
The 2024 report will only show months Jan-May, is it possible that i can add a filter to the 2023 report to only show the month we are in and the previous months of 2023?
This way i will have two bar graphs showing the same data side by side. One showing 2023 and the other 2024.”
Watch us Work Through the Problem
You can watch us excitedly work through the problem right here, if you’d like to see how we approach something like this (and how excited we get when we realize this is something we need in our org as well)! You’ll also get to see what it looks like when I think I’ve beaten Matt to a more elegant solution (fewer lines, fewer characters, way more bragging rights).
Watch us Realize Our Mistake
In the, much shorter, part 2 — you’ll see us intuit that we’ve made an error, confirm that via testing, and then resolve the issue. It is worth noting that Matt’s initial solution worked correctly the first time (consider me knocked down a peg).
The Solution
And, if you’re not the watching type, here’s a textual breakdown of what we ultimately did to produce the desired report and graph!
We:
- added a formula checkbox field to the object (we did it on Opportunities for our example)
- the checkbox is checked if the Opportunity close date (or whatever the relevant date/field will be) is in the “relevant period” of the preceding year.
- To do that the formula looked like this:
AND(
CloseDate <= TODAY(),
TODAY() >= DATE( YEAR(TODAY()), MONTH(CloseDate), DAY(CloseDate) )
)[Replace “CloseDate” with your relevant date field]
This compares the record’s relevant field date with today’s date and month and makes sure the date is less than or equal to that.
Then, simply filter your report by:
- Close Date > (after) 1/1/23 (or whichever years for which you’d like to include the relevant period)
- Created checkbox field is checked
That will show the relevant months in bar chart and, if you group by year, it will show the two periods side by side. Here’s the chart demo portion of the first video, if you’d like a visual example.
Conclusion
Aaand it works! And it’s already live in our org! (But it’s used for confidential numbers so I can’t show you the cool final chart we see in our monthly meetings now).
As always, if you have any questions of your own you’d like to see Matt answer in the hotseat, leave them here in the comments or drop them into the form at www.go.mkp.dev/Q&A !
And, until next time, keep working hard, smart, and happy. And we’ll see you in the cloud.
