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! 

 

  • JackEnviro's avatar
    JackEnviro
    Helper I
    7 years ago

    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