Customers who bought one-time, first time and returned

[juli__sia123412]

Hello all,

Hope someone could help me with this case.

I have table 

OrderID - CustomerID - SKU - Purchase date

( in case of two or more sku were bought, it will be few corresponding records with the same orderID & date)

I want to create a measure and categorize my orders to:

 - One-time ( bought once and never back)

 - First ( first order in case customer bougth again any time later)

 - Repeated ( next orders ) 

Thank in advance!

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[juli__sia123412]

For example -

OrderID -  Customer ID  - SKU        - OrderDate  -TYPE

123        -   456                 - SKU1     - 1/1/2019   - FirstOrder  

123       -    456                -  SKU2     - 1/1/2019   - FirstOrder

156       -    267                - SKU 2     - 1/1/2019   - One-time   

1245     -    456               -   SKU3    - 04/2/2019  - Returned

167      -    456                -   SKU1    - 9/4/2019    - Returned

[v-chuncz-msft · Accepted solution]

@juli__sia123412 ,

You may try the calculated column below.

Column =
VAR r =
    RANKX (
        FILTER ( Table1, Table1[Customer ID] = EARLIER ( Table1[Customer ID] ) ),
        Table1[OrderDate],
        ,
        ASC,
        SKIP
    )
        + RANKX (
            FILTER (
                Table1,
                Table1[Customer ID] = EARLIER ( Table1[Customer ID] )
                    && Table1[OrderDate] = EARLIER ( Table1[OrderDate] )
            ),
            Table1[OrderID],
            ,
            ASC,
            SKIP
        ) - 1
RETURN
    SWITCH (
        TRUE (),
        ISEMPTY (
            FILTER (
                Table1,
                Table1[Customer ID] = EARLIER ( Table1[Customer ID] )
                    && (
                        Table1[OrderDate] <> EARLIER ( Table1[OrderDate] )
                            || Table1[OrderID] <> EARLIER ( Table1[OrderID])
                    )
            )
        ), "One-time",
        r = 1, "FirstOrder",
        "Returned"
    )

[juli__sia123412]

You're genius.

Thanks! It works perfectly!

[Anonymous]

I believe this can be accomplished through using a count function and group functionality. 

1.  Create a calculated column
   1. Column = COUNTX(Table, CustomerID)
2. From there you can create groups in the modeling tab based on the count to create 'bins' based on the quantity.