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

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
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 39 members 1,042 guests