Historical CRM table....determining what the stage of an opportunity was when it converted

[Anonymous]

Hello Community  -  Our SF opportunity history table looks like the following.   We can see the progress of two distinct opportunities...both of which ended converting to a win  (100% - Closed).   And we can see that the probability stage it was at just prior to converting to 100% (i.e. closed won) was 80%.   

Of course not all opportunities convert at 80%.   Some can convert at 60, 70, etc.    No matter what, whatever the probability stage was just before the max createddate is the value we are looking for.   Our goal is to be able to report the frequency of "won" conversions by each particular stage (probability).   So the number of "Won" opportunities that converted at 30%....the number that converted at 40%, etc etc.   Any help is appreciated!

      

[Ashish_Mathur]

Hi,

Could you share a slightly larger dataset (in a format that can be pasted in an MS Excel file) and show the expected result on that dataset.

[Anonymous]

@Ashish_Mathur   Absolutely, thanks Ashish.  

Below is a sampling of the data.   The expected result would be to create a table, probably a matrix, that had two columns:   Closed Won    Closed Lost  

On the rows of that table would be the different probabilities (stages), such as 30%, 40%, etc.    And the values would be the frequency that each of the stages occured, for the respective columns.   Ideally, you could also use the output values in a histogram or some other visual.   Again, the main objective is to show what the frequency of conversion is to either closed won, or closed lost, per stage.   

I would also add that there is one nuance to the data.   Here is a sample.   Where the 0% (representative of a a closed lost opportunity) shows up in two rows.   This is likely because there was some other change made by whoever input the updated record that got captured.   It seems to only occur with opportunities that have been converted to closed lost (0%) and does not always occur.  

OpportunityId	CreatedDate	Probability	ForecastCategory
0065e00000ClR4iAAF	10/1/2021 22:16	80%	Pipeline
0065e00000ClR4iAAF	10/2/2021 23:01	80%	Pipeline
0065e00000ClR4iAAF	10/5/2021 17:44	80%	Pipeline
0065e00000ClR4iAAF	12/13/2021 23:05	80%	Pipeline
0065e00000ClR4iAAF	12/13/2021 23:16	80%	Pipeline
0065e00000ClR4iAAF	1/13/2022 20:34	0%	Omitted
0065e00000ClQhEAAV	10/1/2021 22:16	100%	Closed
0065e00000ClQhEAAV	10/2/2021 22:59	100%	Closed
0065e00000ClkHVAAZ	10/5/2021 14:50	30%	Pipeline
0065e00000ClkHVAAZ	10/5/2021 14:50	30%	Pipeline
0065e00000ClkHVAAZ	12/21/2021 18:48	30%	Pipeline
0065e00000ClkHVAAZ	3/14/2022 0:52	0%	Omitted
0065e00000Clk3EAAR	10/5/2021 12:45	40%	Pipeline
0065e00000Clk3EAAR	10/5/2021 12:45	40%	Pipeline
0065e00000Clk3EAAR	10/5/2021 12:53	40%	Pipeline
0065e00000Clk3EAAR	10/5/2021 13:09	50%	Pipeline
0065e00000Clk3EAAR	10/15/2021 15:58	50%	Pipeline
0065e00000Clk3EAAR	10/15/2021 16:01	50%	Pipeline
0065e00000Clk3EAAR	10/18/2021 18:04	50%	Pipeline
0065e00000Clk3EAAR	10/21/2021 18:33	50%	Pipeline
0065e00000Clk3EAAR	10/27/2021 21:12	80%	Pipeline
0065e00000Clk3EAAR	10/27/2021 21:22	80%	Pipeline
0065e00000Clk3EAAR	10/29/2021 14:26	80%	Pipeline
0065e00000Clk3EAAR	11/11/2021 19:18	100%	Closed
0065e00000CliqTAAR	10/5/2021 0:23	30%	Pipeline
0065e00000CliqTAAR	10/5/2021 0:23	30%	Pipeline
0065e00000CliqTAAR	12/21/2021 17:21	30%	Pipeline
0065e00000CliqTAAR	1/14/2022 2:28	30%	Pipeline
0065e00000CliqTAAR	1/14/2022 2:29	20%	Pipeline
0065e00000CliqTAAR	1/14/2022 2:38	40%	Pipeline
0065e00000CliqTAAR	1/14/2022 2:38	30%	Pipeline
0065e00000CliqTAAR	2/3/2022 18:11	30%	Pipeline
0065e00000CliqTAAR	8/5/2022 14:57	30%	Pipeline
0065e00000CliqTAAR	8/22/2022 8:00	30%	Pipeline
0065e00000ClhQsAAJ	10/4/2021 17:35	30%	Pipeline
0065e00000ClhQsAAJ	10/4/2021 17:35	30%	Pipeline
0065e00000ClhQsAAJ	10/6/2021 14:07	30%	Pipeline
0065e00000ClhQsAAJ	10/26/2021 13:03	30%	Pipeline
0065e00000ClhQsAAJ	10/26/2021 14:11	40%	Pipeline
0065e00000ClhQsAAJ	10/26/2021 14:11	50%	Pipeline
0065e00000ClhQsAAJ	10/26/2021 14:12	60%	Pipeline
0065e00000ClhQsAAJ	10/26/2021 14:12	70%	Pipeline
0065e00000ClhQsAAJ	11/30/2021 17:03	70%	Pipeline
0065e00000ClhQsAAJ	11/30/2021 17:06	70%	Pipeline
0065e00000ClhQsAAJ	11/30/2021 17:06	70%	Pipeline
0065e00000ClhQsAAJ	12/6/2021 15:05	70%	Pipeline
0065e00000ClhQsAAJ	12/6/2021 15:05	70%	Pipeline
0065e00000ClhQsAAJ	1/10/2022 20:24	70%	Pipeline
0065e00000ClhQsAAJ	1/10/2022 20:24	70%	Pipeline
0065e00000ClhQsAAJ	1/22/2022 18:23	70%	Pipeline
0065e00000ClhQsAAJ	1/24/2022 17:01	70%	Pipeline
0065e00000ClhQsAAJ	2/14/2022 19:33	70%	Pipeline
0065e00000ClhQsAAJ	2/14/2022 19:39	70%	Pipeline
0065e00000ClhQsAAJ	2/14/2022 19:46	70%	Pipeline
0065e00000ClhQsAAJ	2/14/2022 19:52	70%	Pipeline
0065e00000ClhQsAAJ	2/14/2022 19:55	80%	Pipeline
0065e00000ClhQsAAJ	2/22/2022 20:43	80%	Pipeline
0065e00000ClhQsAAJ	2/22/2022 21:05	100%	Closed

[Ashish_Mathur]

Hi,

i do not know how much i can help but i would like to try.  If possible, can up enter this data in a workbook and in another tab of that workbook, show the result you want with the help of Excel formulas.  If it is not possible to write the formulas because of complexity, then show the expected figures you want in the other worksheets with an explantion.  I will try to convert those formulas/that logic that into DAX formulas. 

[Anonymous]

@Ashish_Mathur     Hi Ashish  - 

We have Probability from 10 to 80%.   0% indicates closed-lost.    100% indicates closed-won.  

A simple example of the output might be (as a matrix):

                                Closed-Lost     Closed-Won

Probability

60%                               11                      05   

70%                               09                      07

80%                               13                      15

The above is just saying that 13 opportunities converted to closed-lost at 80%, and 15 converted to closed-won.   

Using above as an example, if I were to write the formula logic in plain English, it would be something like:    For each Opportunity ID, on the Max row of "createddate" , if the Probability % = 100, then return the Probability % of the prior row.   (in theory, that prior row is the probability % the opportunity was at when it was converted to closed-won).    Then do the same thing for closed-lost.   The only nuance to this is that sometimes 0% shows up in two rows, so you'd need to have logic that says if the prior row probability % is 0%...AND the prior row is also 0%, then use the next prior row.   

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

@Ashish_Mathur    This is what I love about the Power Bi community!    It is night time here in the USA but I will have a look at this first thing in the morning and get back to you.   From an initial look at the Power Bi file it looks good.....just curiuos if you were able to account for those situations where the last 0% is sometimes preceeded by another 0%?   

[Anonymous]

@Ashish_Mathur    Also, just FYI that "omitted"  =   closed-lost

[Ashish_Mathur]

Hi,

Download the revised PBI file from here.

Hope this helps.

[Anonymous]

@Ashish_Mathur    I modified your source data to show you what I mean about the 0% sometimes repeating twice.    The created date of the first 0% could be just one second before the last created date.   I am not completely sure why this happens in our data set, but it does happen.    I modified the record below to show this example.   How to account for this situation?    Currently,  it shows 2 opportunities at 80% for closed lost  (it is counting the opportunity twice).     Things work great otherwise, but we do have the situation with the 0% happening on some of the opportunities (not sure why but I don't think it is an error with the data...just probably means there was one other event logged in the system immediately prior to the last entry being created).   

[Ashish_Mathur]

Hi,

At 80% probability, the 2 that you see there are of 0065e00000ClhQsAAJ and 0065e00000Clk3EAAR.  Drag OpportunityID to the row labels and you will see what i mean.

[Anonymous]

@Ashish_Mathur    Yes, but if you replicate my real data set, which has this scenario, then you will get 2 for the closed lost for the opportunity ending in 4iaaf.   I mentioned above that I altered your data source in your example to add an additional 0% row.    (which is how some of the rows in my actual full data set are).   

If you do that then you get this: 

[Ashish_Mathur]

Share your new input data in a format that i can paste it in an MS Excel file.

[Anonymous]

@Ashish_Mathur    Hi Ashish  -  Note below in red.   (I did make the created date one second earlier for the first 0%).   

OpportunityIdCreatedDateProbabilityForecastCategory

0065e00000ClR4iAAF	01/10/2021 22:16	80%	Pipeline
0065e00000ClR4iAAF	02/10/2021 23:01	80%	Pipeline
0065e00000ClR4iAAF	05/10/2021 17:44	80%	Pipeline
0065e00000ClR4iAAF	13/12/2021 23:05	80%	Pipeline
0065e00000ClR4iAAF	13/12/2021 23:16	80%	Pipeline
0065e00000ClR4iAAF	13/01/2022 20:33	0%	Omitted
0065e00000ClR4iAAF	13/01/2022 20:34	0%	Omitted
0065e00000ClQhEAAV	01/10/2021 22:16	100%	Closed
0065e00000ClQhEAAV	02/10/2021 22:59	100%	Closed
0065e00000ClkHVAAZ	05/10/2021 14:50	30%	Pipeline
0065e00000ClkHVAAZ	05/10/2021 14:50	30%	Pipeline
0065e00000ClkHVAAZ	21/12/2021 18:48	30%	Pipeline
0065e00000ClkHVAAZ	14/03/2022 00:52	0%	Omitted
0065e00000Clk3EAAR	05/10/2021 12:45	40%	Pipeline
0065e00000Clk3EAAR	05/10/2021 12:45	40%	Pipeline
0065e00000Clk3EAAR	05/10/2021 12:53	40%	Pipeline
0065e00000Clk3EAAR	05/10/2021 13:09	50%	Pipeline
0065e00000Clk3EAAR	15/10/2021 15:58	50%	Pipeline
0065e00000Clk3EAAR	15/10/2021 16:01	50%	Pipeline
0065e00000Clk3EAAR	18/10/2021 18:04	50%	Pipeline
0065e00000Clk3EAAR	21/10/2021 18:33	50%	Pipeline
0065e00000Clk3EAAR	27/10/2021 21:12	80%	Pipeline
0065e00000Clk3EAAR	27/10/2021 21:22	80%	Pipeline
0065e00000Clk3EAAR	29/10/2021 14:26	80%	Pipeline
0065e00000Clk3EAAR	11/11/2021 19:18	100%	Closed
0065e00000CliqTAAR	05/10/2021 00:23	30%	Pipeline
0065e00000CliqTAAR	05/10/2021 00:23	30%	Pipeline
0065e00000CliqTAAR	21/12/2021 17:21	30%	Pipeline
0065e00000CliqTAAR	14/01/2022 02:28	30%	Pipeline
0065e00000CliqTAAR	14/01/2022 02:29	20%	Pipeline
0065e00000CliqTAAR	14/01/2022 02:38	40%	Pipeline
0065e00000CliqTAAR	14/01/2022 02:38	30%	Pipeline
0065e00000CliqTAAR	03/02/2022 18:11	30%	Pipeline
0065e00000CliqTAAR	05/08/2022 14:57	30%	Pipeline
0065e00000CliqTAAR	22/08/2022 08:00	30%	Pipeline
0065e00000ClhQsAAJ	04/10/2021 17:35	30%	Pipeline
0065e00000ClhQsAAJ	04/10/2021 17:35	30%	Pipeline
0065e00000ClhQsAAJ	06/10/2021 14:07	30%	Pipeline
0065e00000ClhQsAAJ	26/10/2021 13:03	30%	Pipeline
0065e00000ClhQsAAJ	26/10/2021 14:11	40%	Pipeline
0065e00000ClhQsAAJ	26/10/2021 14:11	50%	Pipeline
0065e00000ClhQsAAJ	26/10/2021 14:12	60%	Pipeline
0065e00000ClhQsAAJ	26/10/2021 14:12	70%	Pipeline
0065e00000ClhQsAAJ	30/11/2021 17:03	70%	Pipeline
0065e00000ClhQsAAJ	30/11/2021 17:06	70%	Pipeline
0065e00000ClhQsAAJ	30/11/2021 17:06	70%	Pipeline
0065e00000ClhQsAAJ	06/12/2021 15:05	70%	Pipeline
0065e00000ClhQsAAJ	06/12/2021 15:05	70%	Pipeline
0065e00000ClhQsAAJ	10/01/2022 20:24	70%	Pipeline
0065e00000ClhQsAAJ	10/01/2022 20:24	70%	Pipeline
0065e00000ClhQsAAJ	22/01/2022 18:23	70%	Pipeline
0065e00000ClhQsAAJ	24/01/2022 17:01	70%	Pipeline
0065e00000ClhQsAAJ	14/02/2022 19:33	70%	Pipeline
0065e00000ClhQsAAJ	14/02/2022 19:39	70%	Pipeline
0065e00000ClhQsAAJ	14/02/2022 19:46	70%	Pipeline
0065e00000ClhQsAAJ	14/02/2022 19:52	70%	Pipeline
0065e00000ClhQsAAJ	14/02/2022 19:55	80%	Pipeline
0065e00000ClhQsAAJ	22/02/2022 20:43	80%	Pipeline
0065e00000ClhQsAAJ	22/02/2022 21:05	100%	Closed

[Ashish_Mathur]

Hi,

Download the PBI file from here.

Hope this helps.

[Anonymous]

@Ashish_Mathur   That worked....but if I may ask one more thing.   There is a scenario right below the red zeros....you will see two rows with 100%.    I believe in these scenarios in Salesforce the opportunity must have been unexpected and the salesperson just put it in as immediately closed-won  (100%).    So the 1st row is some sort of system entry they did, and they came back a few minutes later to change to make another edit.   In this case, the penultimate row is already at 100%.   

[Anonymous]

@Ashish_Mathur   Here are the rows I am talking about:  

[Ashish_Mathur]

Neither have you told me what the exact result should be nor have you shared data in a format that can be pasted in an MS Excel file.  How do you expect me to help you? 

[Anonymous]

@Ashish_Mathur    Hi Ashish  -  You're help so far has been outstanding!  And the matrix table below is exactly what I was looking for.    This is with the full data set.   Notice that it says there are 2,530 opportunities with no penultimate date - this is because the opportunity was set at 100% for each record of that opportunity.   (see example below).    Likekly these were opportunities that just never went through all of the typical CRM stages so they just got put in as closed-won (100%), but I would still like to show their frequency.    I tried working with your measures and columns today but could not achieve it.    Does this help?  

You could past this table into your source data: 

OpportunityId	CreatedDate	ForecastCategory	StageName	Probability
0065e00000Cs6fdAAB	11/5/2021 11:33	Closed	PO Received	100%
0065e00000Cs6fdAAB	11/5/2021 11:37	Closed	PO Received	100%
0065e00000Cs6fdAAB	11/10/2021 15:50	Closed	PO Received	100%
0065e00000Cs6fdAAB	11/10/2021 15:50	Closed	PO Received	100%
0065e00000Cs6fcAAB	11/5/2021 11:29	Closed	PO Received	100%
0065e00000Cs6fcAAB	11/5/2021 11:29	Closed	PO Received	100%
0065e00000Cs6fcAAB	11/5/2021 11:30	Closed	PO Received	100%
0065e00000Cs6fcAAB	11/10/2021 15:51	Closed	PO Received	100%
0065e00000CrRkMAAV	10/29/2021 16:46	Closed	PO Received	100%
0065e00000CrRkMAAV	10/29/2021 16:46	Closed	PO Received	100%
0065e00000CrRkMAAV	10/29/2021 16:48	Closed	PO Received	100%
0065e00000CrRkMAAV	11/7/2021 10:47	Closed	PO Received	100%
0065e00000CrRkMAAV	11/10/2021 16:21	Closed	PO Received	100%
0065e00000CrRkMAAV	12/6/2021 15:04	Closed	PO Received	100%

[Ashish_Mathur · Accepted solution]

Hi,

Download the revised PBI file from here.  I have pasted the additional rows below the rows which i had in the table of the V2 file.

Hope this helps.

[Anonymous]

@Ashish_Mathur   Hi Ashish  -  It is working great for the 100% (see below).     Where would I adjust the code to also make sure the same situation for the 0% is taken care of?    If you see the last row below for Omitted (closed-lost, 0%) is having the same problem.   I tried replicating part of your code (the IF statements) and just changed the 1 to 0, but that did not seem to work.