Consecutive Count by CustomerId, SubscriptionId & Status

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.