cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Van2050 Regular Visitor
Regular Visitor

Return Customer and New Customer = with no purchase in last 100 days

Hi there,

 

Could someone suggest best way to find New customer and Return customer based on 100 Days rule from today().

I tried few methods, but not happy with the process.

 

New customer = if no order or previous order is older than 100 days.

so june report should show 2 new customers,1 return customer.

cust_numorder id order dateOutput expecting
A10X12004-24-2018New customer
B10X12101-25-2018New customer
C10X12201-26-2018New customer
B10X12303-10-2018Return customer
A10X12405-28-2018Return customer
C10X12506-20-2018New customer
B10X12606-30-2018New customer
A10X12706-30-2018Return customer

 

appreciate your help

 

Thank you

van2050

4 REPLIES 4
Super User
Super User

Re: Return Customer and New Customer = with no purchase in last 100 days

@Van2050

 

Try this calculated column

 

Column =
VAR rule1 =
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[cust_num] ),
            Table1[order date] < EARLIER ( Table1[order date] )
        )
    )
VAR Calc =
    CALCULATE (
        MIN ( Table1[order date] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[cust_num] ),
            Table1[order date] < EARLIER ( Table1[order date] )
        )
    )
VAR rule2 =
    IF ( NOT ( ISBLANK ( Calc ) ), DATEDIFF ( Calc, Table1[order date], DAY ) )
RETURN
    IF ( OR ( rule1 = 0, rule2 > 100 ), "New", "return" )
Try my new Power BI game Cross the River
Super User
Super User

Re: Return Customer and New Customer = with no purchase in last 100 days

@Van2050

 

Please see attached file as well

 

return.png

Try my new Power BI game Cross the River
Highlighted
Van2050 Regular Visitor
Regular Visitor

Re: Return Customer and New Customer = with no purchase in last 100 days

Thank you so much for the reply, i've applied it on my office laptop and it gives me 

" There's not enough memory to complete this operation. Please try again later when there may be more memory available."

 

Let me give a try on my desktop tomorrow, im using Pro license and not expecting limitations from Power BI side. Hope this is a memory issue, ill let you know.

 

Thank you again

Van

 

 

Van2050 Regular Visitor
Regular Visitor

Re: Return Customer and New Customer = with no purchase in last 100 days

Im unabel to load data before April 1st 2017, im getting same memory error even on 16 GB RAM desktop.

Any other way around ?

 

Thank you

Van

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)