Forum Discussion
New and Returning Customer calculated Column issue
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" ))
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
Hi
I'm still stuk with this so wondering if there is any more helpful information I could provide?
Thanks
Jack
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" ))