I actually DO know how to accomplish this by using an additional table, but I was trying to solve it for you with just a single table… which I don’t think can be done in Airtable.
The way to accomplish this with an additional table is to simply link all of your relevant sales records from your “Sales” table to a SINGLE RECORD in a new table called “Monthly Goals”.
So, for example, for all your sales in your “Sales” table that are part of your May 2020 sales goal, link all of those sales records to the exact same record in your “Monthly Goals” table called “May 2020”.
Likewise, for June 2020, all of your sales in your “Sales” table that are part of your June 2020 sales goal would be linked to the same exact record in your “Monthly Goals” table called “June 2020”.
Same thing for each month.
(Note that you will always need to manually link each one of your sales records to your monthly goals table, because there is no automatic linking in Airtable.)
The reason we need to use another table is because summary functions are only available to us when using Rollup fields or Count fields.
So, in that new “Monthly Goals” table, you would create a Rollup field called “Current Sales Total” with the formula:
This would give you the current running total of all the sales for each month’s linked sales.
Then, still in the “Monthly Goals” table, you would create a new currency field called “Total Sales Goal” where you manually type in the sales goal for each month.
Then, still in the “Monthly Goals” table, you would create a very simple formula field called “Amount Left Towards Goal” that would simply be the “Total Sales Goal” minus the “Current Sales Total”.
Then, create a new filtered view in your “Monthly Goals” table that ONLY shows you the CURRENT MONTH that you are tracking. You would need to manually change this filter every month.
Create your Summary Block based on this new filtered view, and display the “Amount Left Towards Goal” field.
Manually type the name of the month onto your Summary Block for everyone to see, which will also give you a visual reminder to yourself to change the filter every month.
That will do the trick for you!