# Compare initial opportunity amount to most recent opportunity amount in reports using formulas

Im trying to do something thats plagued me for years in salesforce reports. It feels like it should be relatively simple but for some reason i struggle to get it to actually work. My actual setup is a little bit different but I'm going to try and use a simpler example of the issue.

Lets say you have an account with a series of opportunities, and what you want to know is the amount change (in a percent) between the initial opportunity and the most recent one, ordered by created date.

My hope is to be able to build a report of Accounts and Opportunities which would allow me to set any date range (Q2 for example) and have a formula field that would calculate the percent change in the amounts between the first opportunity in that range and the last.

I am able to get something similar to this using Opportunity.Amount:Min and Opportunity.Amount:Max but the issue is often times the first opportunity in a date range is not the smallest one, when what i really want is the change between the first and the last.

Feels like I would need some kind of VLookup function or some way to do something like Opportunity.CreatedDate:Min.Amount - Opportunity.CreatedDate:Max.Amount

So a simple example for company Acme Corp has the following opportunities

1. Opp 1 - \$500 - 1/1/2014
2. Opp 2 - \$1250 - 3/1/2014
3. Opp 3 - \$100 - 4/4/2014
4. Opp 4 - \$1000 - 5/7/2014

What im looking for is a way to automatically calculate the difference between the first opp and the last one, so \$1000 - \$500 = \$500 or a 100% increase

The reason i want this in a report is so if i change the date range in the report to be 2/1/2015 - 5/10/2014 it would update the formulas to be \$1000-\$1250 = -\$250

Any ideas?

• You appear to be looking for the `Opportunity.Amount` of the record with the newest CreatedDate - record with the oldest CreatedDate. The Max and Min Amounts have nothing to do with what you're looking for. It would appear that you want the Amounts for the Opps with Max and Min Dates that fall within your range. – crmprogdev May 11 '15 at 1:57
• Yes, that's what I need, is that possible? – Keith Mancuso May 11 '15 at 2:08

You can enable the history for the Amount field and pull a report on the Opportunity History table, and for the given dates you can get the difference in amount which can be later in report can create a formula field to reflect the percentage of the difference.

• But even in that scenerio you could only compare the max and the min value not the earliest (by date) amount to the most recent amount. Right? – Keith Mancuso May 23 '15 at 12:09

Though Not implemented it But can you try following : Create two workflow rules on Opportunity which updates Account :

1)We've to first find if current opportunity is the first Opportunity created for for account and if it is, then somehow capture its amount a)create two field isOpportunityExist(Checkbox, default unchecked) on Account and FirstOppAmount(Currency) on Account b) workflow will fire only if isOpportunityExist = false , after workflow fire , update this field to 'true as well as populate FirstOppAmount field also. Now we've first opportunity amount available on Account

2) create another workflow on Opportunity a) for this also, first create new field say 'latestOppAmount' on Account b) Create new workflow on opportunity ifiring on insert of every new opportunity(formula condition to evaluate workflow will be 'true', which ensure it fires on every insert) , this workflow updates field 'latestOppAmount' with Opportunity amount of every newly created Opportunity now you've , on account' Opportunity amount of most recently created Opportunity,

You can create formula field on Account to compare these two values.

I'm assuming Opportunity amount is not changing after insert. But in case it is changing, 2nd worflow could be changed to fire on every insert/update. in that case, you can compare oldest Opportunity with Opp amount of most recently modified opportunity of that Account.

Hope this helps

• Thanks! Thats similar to what we are doing now. The issue is that we cant get the first and last opportunities within a certain date range like we could with formula fields in reports. – Keith Mancuso May 11 '15 at 4:19
• In that case, I'm not sure, if any standard solution exist. You might have to look for VF page based solution which can solve this – curious_techy May 11 '15 at 4:40