Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Duplicate Transaction Count Excluding First Instance

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
Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Anonymous ,

     

    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.

     

3 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

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

    Repeat Purchases = COUNTROWS('Table') - 1
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    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.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Anonymous 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?