Report showing comparison for sales per month this year vs. last year

What I'm really trying to do is compare sales overall, year to year, by month. So I can say, "last January we did X, this January we did Y, which was a Z% increase." We track estimate value and won value, so I'd like to see how we're comparing w/ the previous year in estimating per month and what we've won per month.

Our sales cycle can be lengthy, so it's a good indicator of how we're moving to watch estimates. Most retail business compare against the same time period the year before, so I assumed I wasn't the first to ask this question, but I couldn't find anyone who really asked the same thing.

OK, so an easy solution is to use excel to load  at able with the values

201801
201802
201803

 and all the way forward say for 20 years.

Set that YYYYMM field to be the key field


Then on the sales table make a field to calculate to the YYYYMM of the sales date

ToText(Year([date of sale]))
&
right("0" & totext(month([date of sale])))


Call it [YYYYMM of Sale Date]

Use that in a relationship to roll that up to the YYYMM table.

Then we also want last year sales

Make a new text formula field for 
[YYYYMM of next year]

ToText(Year([date of sale])+1)
&
right("0" & totext(month([date of sale]))) 

Make a new relationship to the YYYYMM Table  and also make a summary of the sales

You should now have the two fields on the form for the sales of the YYYYMM and also the sales of the YYYMM form last year.

so I would have to use Excel anytime I wanted to see these numbers?

No, you just need to load up a table with all the YYYYMMs for the next 20 years.  But just once.

wow I'm not sure I'm following that. Every time I think I'm doing OK with QB, something like this takes me back down a peg!

Well start into it and post back if you get stuck. :)

The point is that in order to do math between those numbers for total sales they need to hook onto something. In other words they need to be summary fields on a particular record. Do we create those YYYYMM records to capture the totals.

BTW the only data we have is for this current year.

2018 to be clear...

It is a common request but there is no easy answer.