Forum Discussion
New Customer by reference in second table
Hi All I currently have a calculated column on my invoices fact table to identify new customers who meet a minimum spend thresholds which is below. However, our current integration is treati...
Hi
Thanks for looking, I tried the code but I don't think the logic is correct I'm afraid.
I need the "FirstOrder" to be the first order from that ContactID unless the postcode has been used before. I may have got it wrong so please do talk me through your code if you thinkI have.
I am wondering if I could create a column that uses the ContactID unless the postcode has been used before ? Or posssibly a check that if the postcode has been seen before the order is from a return customer possibly like below:
New Customer =
VAR firstorder =
Calculate ( Min (Invoices[Date]) ,
ALLEXCEPT ( 'Invoices', 'Invoices'[ContactId]),
'Invoices'[ContactId] = EARLIER ( 'Invoices'[ContactId] ), FILTER(Invoices ,Invoices[Full Order] = "Full" ))
Var repeatorderpostcode = ?
RETURN
IF (Invoices[Full Order] = "Part", "N/A" , IF ( repeatorderpostcode ?check?, "Return" , IF (firstorder = Invoices[Date], "New" , "Return" )))
Still working on it but any ideas welcome!
Hi All
I think I have a solution but it feels a little complicated.....
I have created a calculated column to bring throught the Postcode to the Invoices table:
Postcode = LOOKUPVALUE(Contacts[Street_PostalCode],Contacts[ContactId], Invoices[ContactId]
I have then repeated the firstorder logic with the post code and updated the lofic so that if the firstpostcode date is after the firstorder date then classed as a return customer (they have purchased before but possibly with different card/cardname). Code is below and I have also updated the file.
New Customer =
VAR firstorderID =
Calculate ( Min (Invoices[Date]) ,
ALLEXCEPT ( 'Invoices', 'Invoices'[ContactId]),
'Invoices'[ContactId] = EARLIER ( 'Invoices'[ContactId] ), FILTER(Invoices ,Invoices[Full Order] = "Full" ))
VAR firstpostcode =
Calculate ( Min (Invoices[Date]) , ALLEXCEPT ( 'Invoices', 'Invoices'[Postcode]),
'Invoices'[Postcode] = EARLIER ( 'Invoices'[Postcode] ), FILTER(Invoices ,Invoices[Full Order] = "Full" ))
RETURN
IF (Invoices[Full Order] = "Part", "N/A" , IF ( firstpostcode > firstorderID, "Return",IF (firstorderID = Invoices[Date], "New" , "Return" ))
)
Does anyone have any suggestions on how to do this without the lookup column?
Thanks
Jack