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

[Anonymous]

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_num	order id	order date	Output expecting
A10	X120	04-24-2018	New customer
B10	X121	01-25-2018	New customer
C10	X122	01-26-2018	New customer
B10	X123	03-10-2018	Return customer
A10	X124	05-28-2018	Return customer
C10	X125	06-20-2018	New customer
B10	X126	06-30-2018	New customer
A10	X127	06-30-2018	Return customer

appreciate your help

Thank you

van2050

[Zubair_Muhammad]

@Anonymous

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

[Zubair_Muhammad]

@Anonymous

Please see attached file as well

[Anonymous]

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

[Anonymous]

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