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

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

      • EnviroJack's avatar
        EnviroJack
        Frequent Visitor
        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" ))