cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Naresh91
Microsoft
Microsoft

How to Count the Lost Customers as per the his Latest status

Hi,

I have the below table

 

Cust-IDCust-StatusDate
1In1/2/2020
2Lost1/5/2020
3In1/3/2020
1Lost1/4/2020
4Lost1/2/2020
2In1/6/2020

 

Want to count the "Cust-ID"s who have "Cust-Status" as both "In" ,"Lost" and Latest "Cust-Status" is "Lost".

 

Output of the above table should be 1 as only "Cust-ID" =1 is having the "Cust-Status" as Both "In and Lost" and  Latest "Cust-Status" is "Lost".

2 REPLIES 2
jameszhang0805
Resolver IV
Resolver IV

Calculate Measure Version:

#LostCus = 
VAR _CusWithNumStatus =
    GROUPBY (
        'Table',
        'Table'[Cust-ID],
        "@NumOfStatus", SUMX ( CURRENTGROUP (), 1 )
    )
VAR _Customer =
    ADDCOLUMNS (
        _CusWithNumStatus,
        "@Status",
            CALCULATE (
                MAX ( 'Table'[Cust-Status] ),
                GENERATE (
                    KEEPFILTERS( VALUES ( 'Table'[Cust-ID] ) ),
                    CALCULATETABLE( TOPN ( 1, VALUES ( 'Table'[Date] ), 'Table'[Date] ) )
                )
            )
    )
VAR _LostCus = FILTER ( _Customer, [@Status] = "Lost")
VAR _LostCus2Status = FILTER(_LostCus , [@NumOfStatus] = 2 )
RETURN
    COUNTROWS( _LostCus2Status )
CNENFRNL
Super User III
Super User III

@Naresh91 , Power Query, DAX and Excel formula, all of them do the trick with ease.

 

Power Query solution,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLMAxJGBkYGugaGugZGSrE60UpGQCGf/OISZBlTsIwxhg5jsLghFh0mYBkTLDIIW1DNMlOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Cust-ID" = _t, #"Cust-Status" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cust-ID", Int64.Type}, {"Cust-Status", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cust-ID"}, {{"Chk", each Table.Sort(_, {"Date", Order.Ascending})}}),
    #"Lost Customer" = Table.TransformColumns(
        #"Grouped Rows", 
        {
            "Chk", 
            each let column = [#"Cust-Status"] 
                in if List.Last(column)="Lost" and List.ContainsAll(column, {"In","Lost"}) then "Lost Customer" else ""
        }
    )
in
    #"Lost Customer"

Screenshot 2021-03-10 042903.png

 

DAX calculated column,

Screenshot 2021-03-10 044745.png

 

Excel array formula, our oldie but goodie, also my favorite,

=IF((SUM(--(IF([Cust-ID]=[@[Cust-ID]],[Cust-Status])={"In","Lost"}))=2)*(INDEX([Cust-Status],MATCH(99999,[Date]/([Cust-ID]=[@[Cust-ID]])))="Lost"),"Lost Customer","")

Screenshot 2021-03-10 045042.png

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors