Tracking version and calculating variance

[John_Townsend]

Hi, I'm trying to build a project cost tracker but I'm reasonably new to airtable. The issue I'm having is that as the project evolves, the costs change (as requirements change etc.). The business wants to be able to see that variance. Currently, when costs change, a new cost estimate record is created. All are linked back to the opportunity (our proposal to the client). However, I can't think of how to calculate the cost variance by cost centre automatically.  

An example of what I'm trying to achieve is below:

	Opportunity (LINKED TO RECORD WITH PRICE TO CLIENT)	COST CENTRE 1	COST CENTRE 2	COST CENTRE 1 CHANGE FROM THE PREVIOUS ESTIMATE	COST 2 CENTRE CHANGE FROM THE PREVIOUS ESTIMATE
Cost Estimate V1 – Deal 1	DEAL1	£500	£100	N/A	N/A
Cost Estimate V2 – Deal 1	DEAL1	£600	£50	£100	-£50
Cost Estimate V1 – Deal 2	DEAL2	£200	£200	N/A	N/A
Cost Estimate V2 – Deal 2	DEAL2	£300	£200	£100	£0

[TheTimeSavingCo]

I think you could achieve this by creating a linked field that links to the same table called "Previous Estimate" or something and link, say, "Cost Estimate V2 - Deal 1" to the record "Cost Estimate V1 - Deal 1"

You'd then use lookup fields to pull the previous estimate's data over, and then use the formula fields to find that difference