Finding first and last product purchased

[OpenMike13]

Hello i have a table that has a membership rate purchased by a contact and has a monthly date if the contact has a membership for the countdate.

ContactID	Countdate	Ratename
1	1/15/2022	Ind. Membership
1	2/15/2022	Ind Membership
1	3/15/2022	Deluxe Membership
2	1/15/2022	Ind. Membership
2	2/15/2022	Ind. Membership
2	3/15/2022	Gold Membership

Im looking to find the first and last product purchased for each contact. So the output should look like 

ContactID	First Product	Second Product
1	Ind. Membership	Deluxe Membership
2	Ind. Membership	Gold Membership

Any help would be appreaciated! 

[v-henryk-mstf · Accepted solution]

Hi @OpenMike13 ,

Try formula like below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUNzTVNzIwMgKyPfNS9BR8U3OTUouKMzILlGJ1IGqM0NRgUWKMpMQlNae0IhXdICMslmFRQsAuIzS73PNzUlBsigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ContactID = _t, Countdate = _t, Ratename = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ContactID", Int64.Type}, {"Countdate", type date}, {"Ratename", type text}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Meb", each if Text.Contains([Ratename], "Ind") then [Ratename] else null),
    #"Filled Down" = Table.FillDown(#"Added Conditional Column",{"Meb"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each not Text.Contains([Ratename], "Ind"))
in
    #"Filtered Rows"

M =
VAR count_f =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER ( ALL ( 'Table' ), 'Table'[Ratename] = MAX ( 'Table'[Ratename] ) )
    )
VAR count_ =
    CALCULATE ( COUNTROWS ( 'Table' ), ALL ( 'Table' ) )
RETURN
    DIVIDE ( count_f, count_, BLANK () )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1]

Hi @OpenMike13 
Here is a sample file with the solution https://www.dropbox.com/t/N6FdYCFWIB4KEhh2

First Product = 
MAXX (
    TOPN ( 1, Sheet1, Sheet1[Countdate], ASC ),
    Sheet1[Ratename]
)

Last Product = 
MAXX (
    TOPN ( 1, Sheet1, Sheet1[Countdate] ),
    Sheet1[Ratename]
)

[OpenMike13]

These measures did as asked, thank you. question would there be a way to find the count/percentages of the combinations? For example the output would look like the table below

	Deluxe Membership	Gold Membership
Ind. Membership	50%	50%

[tamerj1]

Hi @OpenMike13 
Sorry can you explain further this combinations thing?

[OpenMike13]

Looking for an analysis of percetange of people upgrading/downgrading membership based on those rates. Hope that helps