cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Customers who bought one-time, first time and returned

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Customers who bought one-time, first time and returned

@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"
    )

 

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

View solution in original post

5 REPLIES 5
Highlighted
Anonymous
Not applicable

Re: Customers who bought one-time, first time and returned

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. 
Highlighted
Super User IV
Super User IV

Re: Customers who bought one-time, first time and returned

Hi,

Share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Frequent Visitor

Re: Customers who bought one-time, first time and returned

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

 

 

Highlighted
Community Support
Community Support

Re: Customers who bought one-time, first time and returned

@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"
    )

 

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

View solution in original post

Highlighted
Frequent Visitor

Re: Customers who bought one-time, first time and returned

You're genius.

Thanks! It works perfectly!

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors