New Customer by reference in second table

[JackEnviro]

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 treating customers who pay on different cards as new contacts when in fact they are returning customers. I would therefore like to add a filter based on customers with the same Postcode.

The postcode is stored in a sepreate "Customer" table connected by the ContactID field in the invoice table. Has anyone got any ideas on the best way to apply apply this and identify customers who have ordered with the same postcode before?

Thanks 

Jack

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

[v-xicai]

Hi @JackEnviro ,

You can create column like DAX below.

New Customer =
VAR firstorder = Calculate ( Min (Invoices[Date]), FILTER(Invoices, Invoices[ContactId] = EARLIER ( Invoices[ContactId] ) ),
FILTER(Customer, Customer[postcode ] = Invoices[ContactId] ), FILTER(Invoices ,Invoices[Full Order] = "Full" ))
RETURN
IF (Invoices[Full Order] = "Part", "N/A" , IF (firstorder = Invoices[Date], "New" , "Return" ))

Or could you please share your sample data and desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

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

[JackEnviro]

Hi Amy / @v-xicai 

Thanks for your help. I tried your suggestion but it doesn't identify any new customers. 

I have prepared a file to show the tables and relationships:

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

Thanks

Jack

[Mariusz]

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.

[JackEnviro]

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! 

[JackEnviro]

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