After first sale what does customer buy

[Anonymous]

Hello Everyone, 

I am stuck at a level of my analysis and need some support 🙂 

I am currently trying to answer the following question : 

After a new customer purchase a  specific product A do the same customer purchase the product B ? 

My table looks as follow : 

Date	Transaction ID	Email	Item	quantity	price
21/05/2018	1254jkj578	community@gmail.com	2546	1	8
22/05/2018	1254jkj579	community@gmail.com	2546	1	8
23/05/2018	1254jkj580	community@gmail.com	2546	1	8
24/05/2018	1254jkj581	community@gmail.com	2546	1	8
25/05/2018	1254jkj582	community@gmail.com	2546	1	8
26/05/2018	1254jkj583	community@gmail.com	2546	1	8

I used a measure from the member community  :

Measure =

VAR BoughtAfterAcquisition =

CALCULATE (

COUNTROWS ( 'Table1' ),

FILTER (

ALLEXCEPT ( 'Table1', 'Table1[Email], 'Table1d'[Date].[Year] ),

"2016" IN VALUES ( 'Table1'[item] )

&& "2010" IN VALUES ('Table1'[item])

)

)

VAR NOTSO =

CALCULATE (

COUNTROWS ( 'Table1' ),

FILTER (

ALLEXCEPT ( 'Table1',' Table1'[Email], 'Table1'[Date].[Year] ),

AND ( "2016" IN VALUES ( 'Table1'[External ID] ),"2010" IN VALUES ('Table1'[item]))

|| AND ( "2010" IN VALUES ('Table1'[External ID]), "2016" IN VALUES ( 'Table1'[item] ) ) ) ) RETURN

IF ( BoughtAfterAcquisition = 2, "BoughtAfterAcquisition", IF ( NOTSO = 1, "Not So super", "na" ) )

It give me the customer that bought both product, but the issue i have is that some customer may bought the product A without being 'New Customer', i don't know how to add the date of the first order to it so it only calculate customer that bought as first order the product A.

Thank you in advance ! 

Ilhame.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.  Your original table does not have a product column.

[v-cherch-msft]

Hi @Anonymous 

You may try to create the measure like below:

Measure = 
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
        Table1,
        Table1[Product] = "A"
            && Table1[Date]
                = MINX (
                    FILTER ( ALL ( Table1 ), Table1[Email] = MAX ( Table1[Email] ) ),
                    Table1[Date]
                )
    )
)

Regards,

[Anonymous]

Hello Cherie Chen,

Thank you for your reply !!

Unfortunatly when i use both measure ( the one i showed and yours) it's not working at all, i get an error message...

I want to show on a table that customers that buy product A as first product buy after the product B or Not...

Thank you. 

Ilhame

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may create below measures.Then use Final measure in the visual level filter to get the customers that buy product A as first product buy after the product B as second product.

Rank = RANKX(FILTER(ALL(Table1),Table1[Email]=MAX(Table1[Email])),CALCULATE(MAX(Table1[Date])),,ASC)

SecondProduct = CALCULATE(COUNTROWS(Table1),FILTER(ALLEXCEPT(Table1,Table1[Email]),[Rank]=2&&Table1[Product]="B"))

FirstProduct = 
CALCULATE (
    COUNTROWS ( Table1 ),
    FILTER (
      Table1,
        Table1[Product] = "A"
            && Table1[Date]
                = MINX (
                    FILTER ( ALL ( Table1 ), Table1[Email] = MAX ( Table1[Email] ) ),
                    Table1[Date]
                )
    )
)

Final = IF([FirstProduct]=1&&[SecondProduct]=1,1)

Regards,

[Anonymous]

Hi, 

Sorry for my late reply ...

It's worked !!!

thank you so much for the help !!

ilhame