Several ways to do this.
In the attached I used LOOKUP, and I actually didn't need to create the two calcs that I did. I just really needed Calculation1.
I could have done this with LODs too.
PS: Your JPG shows two different calculations. One subtracts. The other divides. I did the subtraction.
Thank you Joe. Your answer was perfect.
Can I ask one more question of you:
-what if I wanted to Sum sales of 2017 Trinkets, but only through the end of the most recent completed month (June)? So I want to SUM(TRINKET Sales) for Jan-Jun 2017, got any ideas what that formula should look like? Then once July is completed, then July should be added to the calc.
I want YTD sales, but offset by a year.
In the attached I modified the 2017 trinkets calc to grab only up through the month prior to today.
This may or may not be sufficient for what you need. If today is the last day of the month (such as July 31), would you want the current month to count? We can do something more than just using TODAY() if necessary.
Look at the calc I displayed in the sheet title; [last order date in data source]. This tells us the largest [order date] in the data. The sample data goes through Dec 2018, but your real data probably doesn't exceed TODAY(), and might likely lag TODAY() by a day if your closing process works that way, (That's how it works where I work.) So today I might only have data through June 29, and therefore today I don't have a closed month of June, and your requirement might want me to grab only through the end of May. (Did I explain that well?)
Anyway, you can tell that trinkets calc where to cut off the data. Depending on what your business does, it might be based on TODAY() or on some other function.
Thank you Joe! Very helpful and much appreciated!