New and Returning Customer calculated Column issue

[EnviroJack]

Hello all

I am trying to identify new customers with a transaction over a threshold (250), this is defined as "Part" or "Full" Order. I want to identify a first time customers with a new order. The below appear to work but not in a number of instance such as the data below example where this is the only example of this customer ID but it is showing a a "Return" customer?

Thanks

Jack

https://drive.google.com/file/d/1FDswetj7PdQN759anEKttKVjKvgyGCXy/view?usp=sharing

[v-yuta-msft]

@EnviroJack ,

I am trying to identify new customers with a transaction over a threshold (250), this is defined as "Part" or "Full" Order. I want to identify a first time customers with a new order. The below appear to work but not in a number of instance such as the data below example where this is the only example of this customer ID but it is showing a a "Return" customer?

---

What does "Part" or "Full" Order and "number of instance" mean? And could you share some details about the logic of achieving "Return"  customer? If possible, please show the expected result.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[EnviroJack]

A "Full" Order is an order over £250, we only want to record a transaction as a  "New" customer transaction when they make a larger purchase above £250  as we also sell sample packs of low value.

The link below is to some real data, there are two invoices in this data (one has multiple lines) and these are the only transactions for this customer.  The second invoice should be recorded as "New" in the New Customer column which is the column this calculated column expression should apply to. I am not sure why it is not and how to correct the expression?

Thanks

Jack

https://drive.google.com/file/d/1FVADuMQMkgXPqsHxJb67L6RiDVEQWp25/view?usp=sharing

[EnviroJack]

Hi

I'm still stuk with this so wondering if there is any more helpful information I could provide?

Thanks

Jack

[EnviroJack · Accepted solution]

I think I've solved this with the following.

Any thoughts on another way to do this welcom. Thanks

New Customer =
VAR firstorder =
Calculate ( Min (Invoices[Date]) ,
ALLEXCEPT ( 'Invoices', 'Invoices'[ContactId]),
'Invoices'[ContactId] = EARLIER ( 'Invoices'[ContactId] ), FILTER(Invoices ,Invoices[Full Order] = "Full" ))

RETURN

IF (Invoices[Full Order] = "Part", "N/A" , IF (firstorder = Invoices[Date], "New" , "Return" ))