cancel
Showing results for
Did you mean:
Helper IV

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

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!

1 ACCEPTED SOLUTION
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
23 REPLIES 23
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

@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
Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

@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.

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

@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%?

Helper IV

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

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

@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).

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

@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:

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

@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
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

@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%.

Helper IV

@Ashish_Mathur   Here are the rows I am talking about:

Super User

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?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

@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:

Super User

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

@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.

Announcements

#### CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

#### Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

#### European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

#### Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors