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

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

@Van2050

Please see attached file as well

Try my new Power BI game Cross the River
Highlighted
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

#### New Topics Started Badges Coming

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

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

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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)