Forum Discussion

JackEnviro's avatar
JackEnviro
Helper I
7 years ago

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...
Need Help
Show and Tell
Mariusz's avatar
Mariusz
Community Champion
7 years ago

Hi JackEnviro 

I've made a small adjustment to your code, let me know if it works for you.

New Customer = 
VAR __FirstInvoiceDate = 
CALCULATE( 
    FIRSTDATE( Invoices[Date] ),
    ALL( Invoices ), 
    VALUES( Contacts[Street_PostalCode] )
)
RETURN 
IF (
    Invoices[Full Order] = "Part", "N/A", 
    IF ( __FirstInvoiceDate = Invoices[Date], "New" , "Return" )
)
Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
Mariusz Repczynski


 

JackEnviro's avatar
JackEnviro
Helper I
7 years ago

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!