Historic data...comparing previous rows

DAX DAX Calculations
1

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! (the below can be pasted into Excel).

In the first 6 rows below, we can see that the opportunity started off at 80%…but eventually ended up at 0% (the equivalent of closed-won).

Our goal is to have a matrix table with two columns: closed-won closed-lost.

The rows would show the stages (10%, 20%…80%). And the values would be the frequency of occurrence that the conversion happened for each of the stages. In other words, how many opportunities flipped to closed-won at 30%, or 40% or whatever percent. Same for closed-lost.

I think the formula will need to find the max createddate (by opportunity ID) which should be the final disposition (either 0% or 100% depending on lost or won), and then Probability on the row prior to that which would be the stage it was at when it converted.

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
2

Hi @richmont ,

Does this have to be DAX, or is Power Query ok? I believe Harsh’s response on the below thread should help you with this. It goes through the process of getting data from the previous line, in your case the probability. From there you can then do some conditional logic on your closed lines.

Greg also has a DAX solution on the same thread.

Calculate days between manufacturing steps - DAX Calculations - Enterprise DNA Forum

1 Like
3

Hi @richmont, did the response provided by @jamie.bryan help in solving your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

4

Hello @richmont, did the response above help solve your query? It’s been a while since we got a response from you. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

5

Hi @richmont, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.