Tips & Tricks: Getting more out of Opportunity History Reports - Answers - Salesforce Trailblazer Community
Trailblazer Community
Ask Search:
Steve MolisSteve Molis 

Tips & Tricks: Getting more out of Opportunity History Reports

User-added image

Opportunity History Reports are f*cking awesome, they allow you to see the complete lifecycle of the Opportunity tracking changes to the "core" Opportunity Metrics: Amount, Stage, Close Date, Probability%, Forecast Category. And unlike Field History Reports they're available right out of the box and immediately have access to historical data from "Day 1" of your SFDC org.  In other words: they work retroactively.  They also have more robust Field Filtering, and access to Cross-Filters (they also don't look like they fell out of the top of the Ugly Tree and hit every branch on the way down).
 
However Opportunity History Reports do have an Achilles’ Heel:  You can’t easily see or filter by the current Stage, Amount, Probability%, Close Date, Forecast Category.  But you can do this (and whole lot more!) just by adding a few simple Formula Fields to the Opportunity Object.  Now some folks are probably saying: 

“I don’t want to add more formula fields, I don’t want my Opportunity to look like a Race Car at the Daytona 500!”. 

Here’s the thing; you don’t need to add the new fields to the Opportunity Page Layout, you just need to give the Users/Profiles “Read” access to the Data in the fields.  So you can keep your precious Opportunity Page layouts as pure as the driven snow in an Ansel Adams landscape.
 
Anyway, it's just an idea that I came up with.  I’m like Leonardo DaVinci, my mind is full of ideas...  although most of them are about as useless as a Wooden Helicopter powered by ropes and pulleys. 
 
And now without further ado here are the fields:
 
Current Stage
Datatype = Formula
Result = Text
Formula = TEXT(StageName)
 
Current Close Date
Datatype = Formula
Result = Date
Formula = CloseDate
 
Current Probability%
Datatype = Formula
Result = Percent 0 decimals
Formula = Probability
 
Current Amount
Datatype = Formula
Result = Currency
Formula = Amount
 
Current Forecast Category
Datatype = Formula
Result = Text
Formula = TEXT(ForecastCategoryName)  

User-added image
Best Answer chosen by Miglena (Salesforce.com) 
Steve MolisSteve Molis
Hi Nate,
If you need a report that shows the current Stage, Close Date, $Amount, Probability% and the historical changes to those fields in 1 Report.    

For eaxmple:  Yuo need a Report of all Opportunities that are currently in the Negotiation/Review Stage with the complete History of Stages, $Amount, Probability, Close Date, etc.  

With a standard Opportunity History Report if you Filter the Report by "To Stage" [equals] Negotiation/Review you'll get any Opportunity that was ever in the Negotiation/Review stage regardless of what Stage they are currently in. 

All Answers

Robert StrunkRobert Strunk
Looking for the "Best Question" button but I don't see it.  Must be a permission thing :-)
Dannielle GivensDannielle Givens
Hey Robert, I had the same issue. I noticed it was with my browser. IE didn't allow me to choose the "Best answer". Try Chrome if you haven't already.
Brian MakasBrian Makas
Really cool, thanks.
Kathy BakalisKathy Bakalis
Thanks Steve. This worked perfectly!
Nathan LindstromNathan Lindstrom
Can you post some examples of how those fields would be useful and what you can do with these reports?
Steve MolisSteve Molis
Hi Nate,
If you need a report that shows the current Stage, Close Date, $Amount, Probability% and the historical changes to those fields in 1 Report.    

For eaxmple:  Yuo need a Report of all Opportunities that are currently in the Negotiation/Review Stage with the complete History of Stages, $Amount, Probability, Close Date, etc.  

With a standard Opportunity History Report if you Filter the Report by "To Stage" [equals] Negotiation/Review you'll get any Opportunity that was ever in the Negotiation/Review stage regardless of what Stage they are currently in. 
This was selected as the best answer
Nathan LindstromNathan Lindstrom
Thanks I can see how those fields could be useful to have!
Steve MolisSteve Molis
No problem Nate

"And if you don't know, now yah know..."
Rachel BolenRachel Bolen
Is there a way to get the Opportunity Quantity field in the Opportunity history report? It seems weird that a standard field is automatically apart of the report type.
Steve MolisSteve Molis
Hi Rachel,
Unfortunately the Quantity field is not cataloged in the Opportunity History Object, and the object is locked (custom fields can't be added to it).  
What are you trying to do with the Quantitiy?  You might be able to use an Opportunity Historical Trending Report.  It's not exacly the same, but it might get you where you wanna be.
Rachel BolenRachel Bolen
We are trying to create reports and dashboards comparing previous month or quarter data to current data. We don't use the Amount field on the opportunity - only the quantity (we track units not $). I didn't see Quantity on the Historical Trending Report either.
Steve MolisSteve Molis
Damn it!  You're right...  I dunno why they would not include it.  I can't think of a Technological Wall since Quantity basically behaves exactly the same way as $Amount (it's a Hybrid Editable/Roll-Up Summary Field).  It's not even included in the standard Opportunity Trends report type. 

The only think I can think of would be to create a custom Number field that "mirrors" the standard Quantity field and is updated via a Workflow Rule or the Process Builder.  
 
Otherwise you're off to the Wonderful World of Report Snapshots...
Rachel BolenRachel Bolen
I was thinking the same thing. I have tried Reporting Snapshots but I can't seem to figure out how to report with the historical data and current within the same report. 
Kim McPeekKim McPeek
We have 3 fields in our opportunity that capture dollar amounts.  Projected amount ($ value when opp is created); Quoted Amount ($ value when quote is sent), and PO Value.  The first 2 get factored by the probabilty and are reported as a weighed dollar value.  I created a custom field called "Report Expected Value" that looks at all 3 fields, does the math, then displays the weighed value.  This is so that there is one field to show on the reports.  This field is on the page layout and visable to all users.  I trield to set it for Opportunity field history - but the field is not included in the list of fields?  Did I miss something as i see all of our other custom fields?
Steve MolisSteve Molis
Hi Kim, can you provide more details?  

Are you referring to Formula fields here => "Projected amount ($ value when opp is created); Quoted Amount ($ value when quote is sent), and PO Value.  The first 2 get factored by the probabilty and are reported as a weighed dollar value.  I created a custom field called "Report Expected Value" that looks at all 3 fields, does the math, then displays the weighed value"

When you say "I tried to set it for Opportunity field history" do you mean you tried to Enable Field History Tracking on the custom field(s)? 

PS.  
This is kinda getting away from my Original Post.  I was simply sharing a way that I had come up with that allowed me to compare the current Stage, CloseDate, $Amount, etc to the Historical Data that is captured in the standard Opportunity History Report.  Custom Field are not cataloged in the standard Opportunity History.


 
Kim McPeekKim McPeek
Hi Steve  Thank you and I am sorry to sway the post from the original topic but this is the closet thread I could find to the issue. 

Yes, this is a formula field.  The formula is "Decimal Places2  
(BLANKVALUE( PO_Amount__c , BLANKVALUE( Quote_Amount__c, Projected_Amount__c )) ) * ( Probability )"

As I am just starting to work with histotical reports, I wnt to Customize > Opportunities > Fields > Set History Tracking (this is where I don't see the field)

Ultimately, I need to capture the value in that field (and a few others) at the beginning of the month then again at the end of the month to show how they change.  If I am coming at this the wrong way please correct me.
Steve MolisSteve Molis
No problem Kim,
Unfortunately Formula Fields can't be selected for Field History Tracking or Historical Trending Reports 

#SorryBabyIhadtoCrashThatHonda 
https://www.youtube.com/watch?v=5lL1ypndnWA&feature=youtu.be&t=47s
Kim McPeekKim McPeek
Awsome movie!!

Plan B:  Is there a way I can push the number from that formula field into a custom field that can be tracked?  I tried to do that via a workflow but I wasn't successful there either.
Steve MolisSteve Molis
What are the PO_Amount__c,  Quote_Amount__c, Projected_Amount__c fields?  Are any of them Formulas?  Basically you'd need to create Non-Formula field that are updated by a Workflow or Process that is triggered any time the key fields are changed
Kim McPeekKim McPeek
Steve - thank you for your help!

No, the 3 fields are not formulas they are currenty

So I created a field that is not a formula called "Expected PO Value".
I created a workflow that triggers if the Report extected value is new or changed (screen shot below)
When I tried to create the New Field Update I don't see/know hoe to complete this - apparently all my current workflows are with picklists.  (see screen shot below for where I am stuck)


User-added image


I'm stuck here

User-added image

 
Steve MolisSteve Molis
Click "Use a formula to set the new value"

You're basically gonna be "converting" your Formula Fields into Fields that are Updated using your existing Formula Logic
User-added image
Kim McPeekKim McPeek
Holly Crap!!! Thank you!  Use a formula to trigger it, a formula to fill it in BUT it isn't a formula so it can be tracked AND trended!!!!!!
Audra KempAudra Kemp

Hi Steve,

I have the Opportunity History Report in place, and it's awesome! Current problem is that if I filter by 'Stage Change = True', no Opportunities still sitting in the first stage are pulled in. Have you solved for this yet? I am already utilizing the 3 extra fields to track current stage duration and have pulled that into the report.

Best,

Audra

Steve MolisSteve Molis
Hi Audra,

Can you post a screenshot of the Report, and the Groupings, Settings, etc, that you're currently using and the results you're getting and create a mock-up of what you want your Report to look like? For questions related to Reports those are really helpful.  

Without being able to see the Report and Filters, it "sounds" like what you want is a Stage History Report that includes both Opportunities that have moved from one Stage to another, and also Opportunties that have never moved out of the initial Stage.  Is that correct, or are you looking for something else?
Audra KempAudra Kemp
Steve,

Thanks for the quick response! Yes, that is exactly what I need: both Opportunities that have moved through stages as well as though that have never moved from the initial stage. 

Here is my current Opportunity History Report (filtered for 1 record type). It only shows 5 records but there are 31 total in the system.

User-added image
Audra KempAudra Kemp
those* that have never moved
 
Steve MolisSteve Molis
Okay, do all Opportunties begin in the same Stage?  Like you have a Validation Rule or something in place that requires all Opportunties to start at "Stage-1", or can a user create a new Opportunity and select any Stage?  
Audra KempAudra Kemp
The beginning stage will change based on record type. Technically we do not have anything in place that requires starting in a specific stage, so the user has flexibility to start the Opportunity at a later stage.
Steve MolisSteve Molis
Ouch... okay, this just got a little bit tougher
Audra KempAudra Kemp
I'm wondering if I should just create fields for each stage and track the date change outside of Opportunity History... might be easier than trying to hack it!
Steve MolisSteve Molis
Wait, I'm not ready to throw in the towel yet, lemme poke around and see what I can do.
Steve MolisSteve Molis
Audra,
Which edition of Salesforce are you using?  Do you have access to Workflows or Process Builder?  I might something that will work 
Audra KempAudra Kemp
Steve,

Enterprise Edition. Yes, I have access to and am very familiar with using Workflows/PB.
Steve MolisSteve Molis
Okay I haven't test this out yet, but I'm wondering if doing something simple like this might work.

1. Create a custom Checkbox field on the Opportunity set to Default = Unchecked/False.  
2. Then create a Workflow or Process triggered whenever the Opportunity Stage is changed and an Immediate Action (Field Update) that sets the checkbox to Checked/True.

Then use a Report Filter Logic like

(1 OR 2) 
1. Checkbox = FALSE
2. Stage Change = TRUE
Audra KempAudra Kemp
I believe this method would still pull in all other Opportunity History changes as well, but I will still build it out to test!

I like your thought process of using a hidden checkbox and PB... just not sure if we can set it to filter out the other record changes automatically tracked in the Opportunity History report.
Steve MolisSteve Molis
Thanks Audra, I didn't know that I even had a "Thought Process"
Natalie LevyNatalie Levy
Hey Steve!  Any thoughts are deduping the Opp History report?  Ideally I am able to filter on how many Opps moved to Qualified in a given week.  Problem is two-fold:

1) It may dupe capture Opps if a rep moves one back and forth (even though I include logic to only capture forward moving opps)

2) I am not able to filter on when the stage changed to the desired stage so have to either filter on latest update or last stage date, both of which are proxies (last update is a better proxy)

Thanks!!
Steve MolisSteve Molis
Hi Natalie,

I usually use Tom Tobin's "Power of One" technique to get the distinct count of Opportunity records 

Tips & Tricks: "The Power of One" the Greatest Formula Ever Written*
https://success.salesforce.com/answers?id=9063000000048RbAAI
Natalie LevyNatalie Levy
Power of One is good stuff! Can I expose that metric in my dashboard view? Ideally my dashboard view is the Power of One calc per stage change. And any thoughts on the other issue I mention? Where the report ideally would represent the deals that were changed to a specific stage in a given week? Thanks!
Steve MolisSteve Molis
Hi Natalie,
Can you post a screenshot of the Report, and the Groupings, and Dashboard Component Settings that you're currently using and the results you're getting and create a mock-up of what you want your DB Table to look like? For questions related to Reports and Dashboards those are really helpful
Fran HawkesFran Hawkes
Hi Steve, I have managed to get a report of opportunities that have moved from one stage to another, but it is giving me more than one opportunity as the owner had moved it twice, how do I get the report to suppress these moves and show only real moves to the stage?  I have stage change = true?
Steve MolisSteve Molis
Okay for that you'll need to use Tom Tobin's "Power of One" technique 

Tips & Tricks: "The Power of One" the Greatest Formula Ever Written* 
https://success.salesforce.com/answers?id=9063000000048RbAAI
Fran HawkesFran Hawkes
Thank you Steve!  Worked a treat.
Thomas SchwennThomas Schwenn
SF support tells me this cannot be done - but I'm looking to detail the # of days a Split Stage sits in.
I currently have a field/formula for "Days at Current Stage" - which resets every time the Split Stage changes. However, I want to show each the time in EACH Split Stage. Ideally, I'd like to use it to include in a Pipeline Movement report - such as the attached image

Here's the architecture:

Standard Object: Opportunties
Custom Object: Opportunity Splits
Field: Split Stage
Split Stages:
  • On Hold
  • In Discussion
  • Proposal/Pitch in Development
  • Qualified
  • Proposal Preliminary
  • Awaiting Decision
  • Project Awarded
  • pipeline movement report

 
Shirley QuShirley Qu
Hello Steve, Hello all,

I kindly need your mighty mind / experience to help me with some challenges, please. Thanks in advance.

Our Sales Management wanted to change / replace existing Opportunity Stage Picklist Values to a different set of values, and at the same time to maintain Stage History record. I saw there're some discussion around how Opportunity History is not properly capturing New Stages (meaning an old value instead of the new value is showing in report). Have you ever encountered this and is there any workaround that will save me from manually manipulate the data? Here is the details:

Existing Stage Picklist Values
1. New (5%)
2. Discovery (20%)
3. Qualify (50%)
4. Proposal (75%)
5. Gain Approval (90%)
6. Won (100%)

New Stage Picklist Values
1. New (5%)
2. Prospect (10%)
3. Qualify (25%)
4. Solutioning (50%)
5. Validation (70%)
6. Gain Approval (90%)
7. Won (100%)

Appreciate any ideas / advice. Thanks so much
Shirley
 
Steve MolisSteve Molis
Unfortunately there is no way to update the From Stage and To Stage values on the existing History records with your new Stage Values
Shirley QuShirley Qu
Thanks Steve, for your prompt response. Much appreciated. 
Have a good day!
Steve MolisSteve Molis
I've created an Unmanaged Package of some of my favorite custom Formula Fields

SteveMo's Custom Field Mix Tape Version 1.0

The package includes:
  • The Power of One (on all Standard Objects)
  • Opportunity Status
  • Current Stage
  • Current Probability%
  • Current Close Date
  • Current $Amount
  • Plus more...
   
Use this URL to install the package into any organization:
https://login.salesforce.com/packaging/installPackage.apexp?p0=04t4P000002EaaG  (https://login.salesforce.com/packaging/installPackage.apexp?p0=04t4P000002EaaG )

Password = UoweB33R

Note: If you are installing into a sandbox organization you must replace the initial portion of the URL with http://test.salesforce.com

The App is free, but if you really like them and are so inclined here's a link to my PayPal (no obligation)  
http://paypal.me/SteveMoForce

Thank you,

SteveMo
Kyle ScheetzKyle Scheetz
Hi Steve - We are trying to do a simple calc of win rate by stage. Is this doable with basic fields that exist on the opportunity record without formulas on the backend? I am hoping that by having a dataset of opportuntities in a range of stages will be the basis for what we need to achieve this.
Steve MolisSteve Molis
That should be possible using the standard Closed and Won checkbox fields, but having The Power of One and the Current Stage or Status will make things a lot easier (especially building any Report Summary Formulas) 
Aggie McCordAggie McCord
Hey, Steve!  I'm not sure if the thread addresses what I'm trying to do using the Opportunity History Report.  I have been asked to provide a report which shows:
  1. How many opps moved from a specific stage to closed/won and closed/lost
  2. How long does it take to get from this specific stage to closed/won
When I run the report, it isn't clear to me that I'm getting the right results - here's a sample of the report I created.  What am I missing?

User-added image
Steve MolisSteve Molis
Hi Aggie,

I think you should be able to do that using some of the custom fields that I created in this post, plus Tom Tobin's "Power of One" technique =>

Tips & Tricks: "The Power of One" the Greatest Formula Ever Written* 
https://success.salesforce.com/answers?id=9063000000048RbAAI 



Are you looking for something kinda, sorta, like this?
User-added image
Aggie McCordAggie McCord
Hey Steve - thanks for responding!  I believe the chart you provided answers the first requirement which is fantastic.  I just want to make sure I clearly understand what the report shows.  When an opp reaches Value Prop stage, the win rate is 73% while loss rate is 26.7%, correct?

I'll read through the thread to get the setup deets!
Aggie McCordAggie McCord
Sorry, Steve, but I need more help on this one.  I alread had the PO1 field (I found your thread on that one a few years ago!) and created the fields you laid out; however, I'm struggling w/the summary formulas - do you mind sharing the summary formulas you used for the win rate and loss rate?
Steve MolisSteve Molis
Hi Aggie here are the Formulas and settings that I used (they're actually easier to build in Classic)

1
User-added image
2
User-added image
3
User-added image
Aggie McCordAggie McCord
Ok - this is really helpful but I'm not there yet - sorry, Steve!  Lost doesn't up in my list of fields to choose from - is it a custom field?
Steve MolisSteve Molis
Which field?  

Opportunity.Opportunity__c is the API Name of my Opportunity Power of One field

Can you post a screenshot? 
 
Aggie McCordAggie McCord
  1. Won is a field I can choose from standard fields when adding a column to the report but Lost is not.  I was asking if you created a custom field for Lost.
  2. Did you create bucket fields for Status in order to group by column?
  3. Here's a snapshot of my report so far and it is very far from the report you created.User-added image
Steve MolisSteve Molis
Oh, that's just a custom Formula(Text) field that I created on my Opportunity object called Opportunity Status. 

My org has been around since 2003, way before Report Buckets were even a thing, so it simplify my Reports (and maintain my sanity) I created a Formula(Text) field like this

Fieldname = Status
Type = Formula 
Result = Text 
Formula = 
IF(IsWon = TRUE, 
"Won", 
IF(IsClosed = FALSE, 
"Open",
"Lost"))
I also created custom Formula(Checkbox) fields to get the distinct count of Open and Lost and make wrting Report Summary Formulas easier

Fieldname = Open
Type = Formula 
Result = Checkbox 
Formula = 
IsClosed = FALSE
Fieldname = Lost
Type = Formula 
Result = Checkbox 
Formula = 
AND(
IsClosed = TRUE, 
IsWon = FALSE
)

Otherwise you can do it in a Report Summary Formula by subtractind the WON:SUM from the CLOSED:SUM like this
( CLOSED:SUM - WON:SUM )  /  CLOSED:SUM




 
Aggie McCordAggie McCord
Ok - that makes sense!  I did create a fomula field for lost but I still couldn't get my report to look like yours.  I will update but I know I'll be back for one more question!

I cannot thank you enough!
Steve MolisSteve Molis
No problem Aggie, I'll keep your tab running at @Who owes me a beer
Steve MolisSteve Molis
PS.  For the report that you described, you should be filtering to only resutrn Opportunities that are Closed, so unless your org has mutliple Stagenames for Closed/Won and Closed/Lost you could just group it by my custom Current Stage formula field at the top of this thread 
Aggie McCordAggie McCord
I'm pretty sure it's a year's worth of beer for this!
Steve MolisSteve Molis
I'll keep your tab open at Who owes me a beer ;-D
Aggie McCordAggie McCord
You have quite the list of people who owe you a beer - impressive!
 
Aggie McCordAggie McCord
Ok - I finally got back to the report!  I created test records and then validated the data.  It seems to me that the stats reflect the intial From Stage.  For example, if I'm looking for stats on how many are either created at or reach Stage 3 to closed / won or closed / lost, this report as is won't provide that information.  Is that correct?
Peter MartensenPeter Martensen
Steve, I'm creating a report to show Opportunities where the Probablility hasn't changed in more than 30 days.  I am getting multiple lines for each Opportunity as it changes Stages/Probabilities.  Can you suggest a way to filter out all of the lines except the line with the most current Stage?  I haven't been able to figure this out.
Steve MolisSteve Molis
@Peter Martensen

How are you capturing the Date of the last Opportunity Probability% change?

Can Users manually edit the Probility% without changing the Opportunity Stage on your org?  Or are they using the default Probability for each Stage?
Peter MartensenPeter Martensen
Steve,  the "Last Stage Change Date" is just a choice in the Opportunity History report type.  I don't think I did anything to get that data.  My Users can manually edit the Probability.  It is not tied to the Stage.
Steve MolisSteve Molis
Okay what you're looking for is outside the scope of an Opportunity History Report because it does not capture the Last Probability % Change Date. 

For that you'd need to create a custom Date or DateTime field and then use Process Builder or Workflow to update it every time the Proabaility % is changed.

Otherwise you might be able to use an Opportunity Historical Trending Report instead 

Documentation Opportunities with Historical Trending Report
https://help.salesforce.com/articleView?id=reports_opps_trending.htm&type=5 (https://help.salesforce.com/articleView?id=reports_opps_trending.htm&type=5)
 
Peter MartensenPeter Martensen
Steve, is there a way to only show the most recent "Last Stage Change Date"?
Steve MolisSteve Molis
I dunno what you mean by "most recent Last Stage Change Date"

Last and Most Recent are basically the same thing, can you clarify the difference (if there is one)?

The Last Stage Change Date is available right out of the box in a standard Opportunity History Report

 
Peter MartensenPeter Martensen
Right.  But I'm seeing several lines for each Opportunity as it is moved from one Stage to another.  I would like to show only the most recent Last Stage Change Date instead of all of them.  These are complicated by the fact that the Stage was changed more than once in a day.  
User-added image
Steve MolisSteve Molis
Wait...  are you asking to filter the Report so that it only shows the To/From Stage History of the most recent Stage Change, excluding all of the History before that?
Peter MartensenPeter Martensen
Yes.  That sounds like what I want.
Steve MolisSteve Molis
For that you'd need to use something like Field to Field Filtering
Arced RemolloArced Remollo
Hi, Newbie here (T⌓T) Do you have any idea on how to customize Opportunity History report type? 
I am trying to create a report for Opportunity Pipeline with filter of Account:Created by. I have already tried to make joined report by combining opportunity history and account but I cannot achieve the opportunity stage funnel as it is not available on the "group across block". Hope you can help me (〒︿〒)
Steve MolisSteve Molis
@Arced Remollo 

That sounds like something that you could do with a simple Formula(Text) field 

Create a new custom field on the Opportunity Object and select

Datatype = Formula
Result = Text
Formula = 
Account.CreatedBy.FirstName +' '+ Account.CreatedBy.LastName

 
Arced RemolloArced Remollo
This works on my report :)  Thank you so much @Steve Molis ! You're the best! 
 
Kaleigh GarciaKaleigh Garcia
Hey, Steve! I am looking to create a process builder that will update my date field when an opportunity leaves a stage. Can you assist with what the formula might look like? Thanks in advance! 
Steve MolisSteve Molis
Hi @Kaleigh Garcia 

Your Process trigger would simply be
 
ISCHANGED([Opportunuity].StageName)
That's it

Then just add an Immediate Action that updates your custom Date Field using the Formula
TODAY()
That's all you need

 
Kaleigh GarciaKaleigh Garcia
@Steve, Probably a dumb question, but what if it's a specific stage and they are picklist values?
Steve MolisSteve Molis
Hi @Kaleigh Garcia , not a dumb question at all

Specific Stage that they are changing it "From"?
Specific Stage that they are changing it "To"?
Or both?
Kaleigh GarciaKaleigh Garcia
@Steve

They would be changing it from our "Needs Analysis" stage (which this is the date I want to capture) to a "Proposal" stage. However, our opp's can backtrack if something changes. So, I would like to be able to capture the date anytime an opportunity leaves the "Needs Analysis" stage, please! Thanks for all of your help! I know you're busy around here. 
Steve MolisSteve Molis
Okay then it would just be like
 
AND(
ISCHANGED([Opportunuity].StageName),
TEXT(PRIORVALUE([Opportunuity].StageName)) = "Needs Analysis" 
)

 
Kaleigh GarciaKaleigh Garcia
@Steve, THANK YOU! I do have one more question if you don't mind? I also need a field that continuously calculates the number of days that an opportunity has been given a specific picklist value. 

For instance, we have engineers that will be assigning picklist values to opp's as they move them through their internal evaluation process (Not the typical opportunity stage) and will be using a picklist field to determine the stage. I essentially need a field that references that picklist field and calculates the number of days that a specific value has been assigned to the opportunity. Any ideas? 
Steve MolisSteve Molis
@Kaleigh Garcia 

Tell your boss to crack open their piggy bank, cuz they owe me...  ;-D 

Anyway that's totally doable, you just need a custom Date or date Time field to record when the Picklist was changed to Value = X and another custom Date or date Time field to record when the Picklist was changed to something other than Value = X and 

Then you need a custom Formula(Number) field like this
 
BLANKVALUE( X_End_DateTime__c , NOW() ) - X_Start_DateTime__c

 
Kaleigh GarciaKaleigh Garcia
@Steve, hahaha you're the man! Thanks so much, I greatly appreciate your assistance! Have a great weekend (: 
Steve MolisSteve Molis
No problem, you too
Steve MolisSteve Molis
PS. You owe me a beer! ;-p
Sven CuneSven Cune
Hi Steve!
I owe you a few crates of beer. I read through a few posts in regards to reporting on opportunity conversion rates, which has been super helpful. One issue remains which I cannot solve, which is how to also take into account opportunities which did not move to another stage yet. I have asked this questions here: 
https://trailblazer.salesforce.com/answers#!/feedtype=SINGLE_QUESTION_DETAIL&dc=Reports_Dashboards&criteria=OPENQUESTIONS&id=9063A000000tLa5QAE

I would be awesome if you can take a look at it!

Cheers,
Sven
Steve MolisSteve Molis
@Steve Molis

That is one way that you could use the Current Stage Formula field.  

Also if the Stage was never changed for the original Stage that was selected when the Opportunity was created the From Stage would be Blank and the To Stage would represent the current Stage
Ali MahmoodAli Mahmood
Hi Steve,

I need some help on Last Modified date and Last Stage Change date as well as multiple changes to a specific stage for the Opportunity History Report. Would appreciate any guidance. Thank you.

https://trailblazers.salesforce.com/answers?id=9064V0000015AZ5QAM
Steve MolisSteve Molis
@Ali Mahmood

Unfortuately you won't be able to do that with just a Report Filter.  You'll need a custom field to record the date of the Stage change, and update it with Process Builder or Workflow
Hector RojasHector Rojas
Hi,

I would like to know if it's possible to create a historic report that shows me the opportunities that were committed with the close date in the last quarter or any quarter in the past but for any reason, those opportunities change in the close date and slipped to (this quarter or future quarters)  I tried with "opportunity with historical trending" report but salesforce only shows the last 3 months, so if I want to see something that change in April in close date e.g 04-15-2020 Q2 to 10-05-2020 Q4, I cant see it today. Also, I tried with Opportunity field history but I can't filter by ( opportunities that were expected to close last quarter and change the close date to the future) I appreciate your help.

For your information, I use salesforce classic and I don't have developer access.
Steve MolisSteve Molis
Hector,
Can you post a screenshot of the Report, Settings, and Filters that you're using, the results you're getting, and a mock-up of what you want?  ​​​​​​​For questions related to Reports and Dashboards those are really helpful
Emma KeelingEmma Keeling
Hi Steve, sorry to jump in on an old thread, hope you see this...does the "Stage Change = True" resolve the challenge in your response marked as "Best Answer" as with this you only see the relevant stage change? I'm trying to figure out exactly how it functions...Thanks! Emma
Steve MolisSteve Molis
@Emma Keeling 

Yes, I always use Stage Change = TRUE, unless I am specifically looking for a change to Close Date or $Amount (but I usually use Historical Trending Reports for those) 
Katie KraheKatie Krahe
Hi! Is there a way to show all opportunities open in a sales person pipeline but just show the lastest change for the close date field?  I tried an opportunity history report but it is showing multiple rows for 1 opportunity with each time the date changed. 
 User-added image
 
Steve MolisSteve Molis
Hi Katie, is that an "Opportunity History" report?  or an "Opportunity Field History" report? 

That looks like an "Opportunity Field History" report, which is an entirely different animal, and not really intended for analytics, they's really meant for a forensic data audit (and not much else)
Katie KraheKatie Krahe
It is opportunity field history report.  Should I use opportunity history report instead? Or do you have another recommendation to show above?
Steve MolisSteve Molis
Yes, but an Opportunity History Report will not be able to be filtered to show you only the most recent change to the CloseDate field either.  

For that you'd need to create a custom Date field and update it via Process Builder, Workflow, or Flow using a trigger formula like this
ISCHANGED( CloseDate )