Identify first time customer purchased one SKU

[mboschbe]

Hello!

I would like to identify when a customer had purchased a sku for the first time and when had purchased for the second time (as a replacement). 

My table is as following:

ITEM CODE	DATE	QTY	ORDER ID	CUSTOMER ID	TYPE
A	01/01/2019	2	01	C06	1st
B	01/01/2019	1	01	C06	1st
C	01/01/2019	1	01	C06	1st
A	01/05/2019	1	075	C06	REPLACEMENT
D	01/05/2019	1	075	C06	1st

Is it possible to do it? Can anybody help me out? SO far I've been only able to identify first sales order of a customer.

Thanks!

[v-juanli-msft]

Hi @mboschbe 

It works with amitchandak's forluma

Column =
COUNTX (
    FILTER (
        'Table',
        'Table'[ITEM CODE]
            = EARLIER ( 'Table'[ITEM CODE] )
            && 'Table'[CUSTOMER ID]
                = EARLIER ( 'Table'[CUSTOMER ID] )
            && 'Table'[DATE]
                <= EARLIER ( 'Table'[DATE] )
    ),
    [ORDER ID]
)

Column 2 = IF([Column]=1,"1st","replacement")

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mboschbe]

Hello!

I have  little problem when using this formula, is not about the formula is about how data is set.

A customer places two orders same day to buy for the first time one SKU. Customer orders two units,same SKU different orders but same date.

My date data for both orders is showed as 13/01/2020 (00:00)

When applying formula, it gives me that both units are replacement so in this case for this customer I don't have a 1st purchase.

Do you think it will be possible to update formula in order to identify one of the orders as 1st? or both orders as 1st?

Many thanks!

[v-juanli-msft]

Hi @mboschbe 

Column = 
CALCULATE(DISTINCTCOUNT('Table'[DATE]),
    FILTER (
        'Table',
        'Table'[ITEM CODE]
            = EARLIER ( 'Table'[ITEM CODE] )
            && 'Table'[CUSTOMER ID]
                = EARLIER ( 'Table'[CUSTOMER ID] )
            && 'Table'[DATE]
                <= EARLIER ( 'Table'[DATE] )
    ))

Column 2 = IF([Column]=1,"1st","replacement")

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mboschbe]

Hello!

Thanks for your reply.

I tried applying new formula but power bi says processing but it never finishes calculating (two hours calculating already).

Is it may be because the same data base contains many different customers? Or it can be only because data base is big and it just needs more time? 

Many thanks!

[mboschbe]

Hey!

Many thanks for your help and quick response. All works 🙂

[amitchandak]

You can create a new column of count and use that to find new and replacement

count of sales = countx(filter(table, table[ITEM CODE] =earlier(table[ITEM CODE])
							&& table[CUSTOMER ID] =earlier(table[CUSTOMER ID])
							&& table[DATE] <=earlier(table[DATE])),table[ORDER ID])

Appreciate your Kudos.