cancel
Showing results for
Did you mean:
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_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

Thank you

van2050

4 REPLIES 4
Highlighted
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" )```
Super User

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

@Van2050

Please see attached file as well

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

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

Announcements

#### 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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 123 members 1,740 guests
Recent signins: