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
v-xicai's avatar
v-xicai
Community Support
7 years ago

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.

  • 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