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
- Opp 1 - $500 - 1/1/2014
- Opp 2 - $1250 - 3/1/2014
- Opp 3 - $100 - 4/4/2014
- 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