Duplicate Transaction Count Excluding First Instance

[win_anthony]

The goal is to count the number of transactions a customer has purchased the same product excluding their very first purchase of the product. Below you will find sample data and expected output. I cannot figure out how to exclude the customer's very first purchase of a product. Any advice on how to achieve this is greatly appreciated. 

Sample Data 

trans_id	cust_id	prod_id	prod_name	date
1	100	A	Pizza	Dec 1, 2022
2	100	B	Sushi	Dec 1, 2022
3	100	A	Pizza	Dec 3, 2022
4	200	B	Sushi	Dec 4, 2022
5	100	A	Pizza	Dec 4, 2022
6	200	B	Sushi	Dec 6, 2022
7	100	A	Pizza	Jan 1, 2023
8	200	A	Pizza	Jan 1, 2023
9	100	B	Sushi	Jan 1, 2023
10	100	A	Pizza	Jan 2, 2023

Expected Output *Notice cust_id = 100, Pizza was purchased 3 times in December 2022 but expected count = 2 (bc excluding the first transaction) and January 2023 = 2*

month_year	cust_id	prod_id	prod_name	expected_count
Dec 2022	100	A	Pizza	2
Dec 2022	200	B	Sushi	1
Jan 2023	100	A	Pizza	2
Jan 2023	100	B	Sushi	1

[v-rzhou-msft · Accepted solution]

Hi @win_anthony ,

Here I create a sample to have a test.

Tables:

DimDate =
ADDCOLUMNS (
    CALENDARAUTO (),
    "month_year", FORMAT ( [Date], "MMM YYYY" ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] )
)

Relationship:

Measure:

Repeat Purchases =
VAR _LASTDATE =
    CALCULATE (
        MIN ( 'Table'[date] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[cust_id], 'Table'[prod_id], 'Table'[prod_name] ),
            'Table'[date] < MAX ( 'Table'[date] )
        )
    )
VAR _COUNTROW =
    COUNTROWS ( 'Table' )
RETURN
    IF (
        _LASTDATE
            IN VALUES ( DimDate[Date] )
                || _LASTDATE = BLANK (),
        IF ( _COUNTROW - 1 <= 0, BLANK (), _COUNTROW - 1 ),
        _COUNTROW
    )

Result is as below.

Best Regards,
Rico Zhou

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

[win_anthony]

@v-rzhou-msft thank you so much for your support. This worked but when put into a table, the totals are not adding correctly. Ex: The expected total for measure "Repeated Purchases" should = 6 (this is what we want). Once we turn on the visual total, it now = 9 (this is what we are getting). Any advice on how to have the expected total reflected?

[johnt75]

If you want to exclude the first purchase in each period then I think you can just use

Repeat Purchases = COUNTROWS('Table') - 1