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 ca...
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.

     

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