Forum Discussion

EnviroJack's avatar
EnviroJack
Frequent Visitor
7 years ago
Solved

New and Returning Customer calculated Column issue

Hello all

 

I am trying to identify new customers with a transaction over a threshold (250), this is defined as "Part" or "Full" Order. I want to identify a first time customers with a new order. The below appear to work but not in a number of instance such as the data below example where this is the only example of this customer ID but it is showing a a "Return" customer?

 

Thanks

Jack

 

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

Need Help
  • EnviroJack's avatar
    EnviroJack
    7 years ago

    I think I've solved this with the following.

     

    Any thoughts on another way to do this welcom. Thanks

     

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

4 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Community Support
    7 years ago

    EnviroJack ,

     

    I am trying to identify new customers with a transaction over a threshold (250), this is defined as "Part" or "Full" Order. I want to identify a first time customers with a new order. The below appear to work but not in a number of instance such as the data below example where this is the only example of this customer ID but it is showing a a "Return" customer?

    What does "Part" or "Full" Order and "number of instance" mean? And could you share some details about the logic of achieving "Return"  customer? If possible, please show the expected result.

     

    Community Support Team _ Jimmy Tao

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

    • EnviroJack's avatar
      EnviroJack
      Frequent Visitor
      7 years ago

      A "Full" Order is an order over £250, we only want to record a transaction as a  "New" customer transaction when they make a larger purchase above £250  as we also sell sample packs of low value.

       

      The link below is to some real data, there are two invoices in this data (one has multiple lines) and these are the only transactions for this customer.  The second invoice should be recorded as "New" in the New Customer column which is the column this calculated column expression should apply to. I am not sure why it is not and how to correct the expression?

       

      Thanks

      Jack

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

      • EnviroJack's avatar
        EnviroJack
        Frequent Visitor
        7 years ago

        Hi

         

        I'm still stuk with this so wondering if there is any more helpful information I could provide?

         

        Thanks

        Jack