Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi PBI Community!
I have a table of data similar to the below - and am looking to get a count of consecutive closed-lost opportunities by CustomerId and SubscriptionId.
I've seen a couple of consecutive count tutorials, but none that also factor in an additional layer of 'by customer' and 'by subscription' as well since a customer can have more than one subscription at a given time.
Basically, once a customer has four consecutive months of a closed-lost subscription, their overall subscription moves to cancelled -- so, I'm trying to flag those that might be on the cusp of an overall cancellation.
CustomerId | SubscriptionId | CloseDate | Amount | Stage |
CustomerA | 1A | 3/1/2021 | $10 | Closed-Lost |
CustomerA | 1A | 2/1/2021 | $10 | Closed-Lost |
CustomerB | 2A | 1/1/2021 | $5 | Closed-Won |
CustomerA | 1A | 1/1/2021 | $5 | Closed-Won |
As of 1/1, Cust A has 0 Closed-Lost opps, but as of 3/1, Cust A now has 2 consecutive closed-lost opps. Unsure if a conditional column or measure makes more sense here, but any ideas are greatly appreciated.
Many Thanks!
Solved! Go to Solution.
Hi @samdep
I have a solution for this scenario,
(1) Create a column in Power Query,
M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcgRi55z84tQUBZ/84hIgT8XIAEga6xvpGxkYGQGZJkqxOtFK5haW2JWbQpQbwpQbKoDV4zfeCGE8EaoNSVMNNdwQp/Lw/DyEakOSVBuQotoSTbGJqRmQ6YTpbkNoGMK9aUyEeiMk9UQoNySoHOp0Q2ggIiuPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DonorId = _t, SubscriptionId = _t, StageName = _t, OpportunityAmount = _t, CloseDate = _t, #"Goal Column or Measure" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DonorId", type text}, {"SubscriptionId", type text}, {"StageName", type text}, {"OpportunityAmount", Currency.Type}, {"CloseDate", type date}, {"Goal Column or Measure", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"CloseDate", Order.Ascending}}),
#"Add Column"= Table.AddColumn(#"Sorted Rows", "Count", (r) => if r[StageName] = "Closed Lost"
then List.Count(
List.LastN(
Table.SelectRows(
#"Sorted Rows",
each [DonorId] = r[DonorId] and [SubscriptionId]=r[SubscriptionId] and [CloseDate] <= r[CloseDate]
)[StageName],
each _ = "Closed Lost"
)
)
else 0)
in
#"Add Column"
then it returns a column
(2) then create a calculated column with DAX code
Column =
var _closedate= CALCULATE(MAX('test'[CloseDate]),FILTER('test','test'[DonorId]=EARLIER('test'[DonorId]) && test[SubscriptionId]=EARLIER( test[SubscriptionId]) && test[Count]>0))
return IF(test[CloseDate]= _closedate,test[Count],BLANK())
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Hi @littlemojopuppy - thanks for your response! I don't think I have the ability to attach a pbix file, but I sent it to myself as a wetransfer link https://wetransfer.com/downloads/57c7819c26fae486a3639749e843940020220312215902/ea00416c805bf49cb9c7...
Hopefully, you can access this sample data. I was thinking that if I could create a conditional column or measure that places the count of consecutive closed-lost opportunities on the max date record by donor, that would solve for my need - ie: doesn't need to be a running count on every record, just the latest or max date by donor. Thanks again for looking into this!
Hi @samdep
I have a solution for this scenario,
(1) Create a column in Power Query,
M code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRcgRi55z84tQUBZ/84hIgT8XIAEga6xvpGxkYGQGZJkqxOtFK5haW2JWbQpQbwpQbKoDV4zfeCGE8EaoNSVMNNdwQp/Lw/DyEakOSVBuQotoSTbGJqRmQ6YTpbkNoGMK9aUyEeiMk9UQoNySoHOp0Q2ggIiuPBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DonorId = _t, SubscriptionId = _t, StageName = _t, OpportunityAmount = _t, CloseDate = _t, #"Goal Column or Measure" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"DonorId", type text}, {"SubscriptionId", type text}, {"StageName", type text}, {"OpportunityAmount", Currency.Type}, {"CloseDate", type date}, {"Goal Column or Measure", type text}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"CloseDate", Order.Ascending}}),
#"Add Column"= Table.AddColumn(#"Sorted Rows", "Count", (r) => if r[StageName] = "Closed Lost"
then List.Count(
List.LastN(
Table.SelectRows(
#"Sorted Rows",
each [DonorId] = r[DonorId] and [SubscriptionId]=r[SubscriptionId] and [CloseDate] <= r[CloseDate]
)[StageName],
each _ = "Closed Lost"
)
)
else 0)
in
#"Add Column"
then it returns a column
(2) then create a calculated column with DAX code
Column =
var _closedate= CALCULATE(MAX('test'[CloseDate]),FILTER('test','test'[DonorId]=EARLIER('test'[DonorId]) && test[SubscriptionId]=EARLIER( test[SubscriptionId]) && test[Count]>0))
return IF(test[CloseDate]= _closedate,test[Count],BLANK())
Best Regards,
Community Support Team _Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |