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

Question

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?

Answer 1

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.

Answer 2

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