Forum Discussion

ADSL's avatar
ADSL
Post Prodigy
2 years ago

Calculation New, Repeat Customer

Hi BI Community Team,   I have a sales order table (Aug'23 till Oct'23), with this I have created a measure below to find the customer purchased:   Customer Purchased = CALCULATE(DISTINCTCOUNT('...
Need Help
ADSL's avatar
ADSL
Post Prodigy
2 years ago

Hi Ashish_Mathur,

 

After revised and follow your new measure, then I am mapping with manual calculation. It's different.

 

Screenshot below, I am doing in manual by mapping between sales order in Sep'23 and Oct'23.

 

> Customer purchased => customer have order

> Repeat => custoemr have order in current month and last month.

> New => customer have order in current month and never have any order in last month

> These calculation are required to excluded == Sales Order'[InvoiceType] = "F2", 'Sales Order'[OrderType] = "SALE")

 

Please kindly help!

 

 

Sample link - https://drive.google.com/drive/folders/1UzgG-CXW3G0diCSfILUYRqXq9piZWZLj?usp=sharing 

Thanks and Regards,

Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

My formula is correct.  Please check the data thoroughly.

  • ADSL's avatar
    ADSL
    Post Prodigy
    2 years ago

    Hi Ashish_Mathur ,

     

    When I copy the list of customer code order in Oct'23 and match the customer code order in Sep'23, if found is "Repeat" and Not found is "New" then see the result below.

     

    Is it possible that still count from these column Sales Order'[InvoiceType] = "F2", 'Sales Order'[OrderType] = "SALE"? 

     

     

    Any suggestion/advise?

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      2 years ago

      Hi,

      Prepare a simple Table visual as seen in the screenshot below.  See which Customer codes are not being identifies correctly.  DIY.

       

      • ADSL's avatar
        ADSL
        Post Prodigy
        2 years ago

        Hi Ashish_Mathur,

         

        I have followed your advise then found the result after choose a few customer to mapping with manual.

         

        1. New --- customer code: CT01718 have one transcation in Oct'23 but your calculation is showing in "Repeat". It should be in "New"

         

        2. Repeat --- customer code: CT03498 have 02 transcation in Oct'23, your calculation is showing "Repeat", it should be in "New" because transcation are in the same month.

         

        => Even customer have one order or many in the same month, we still call "New" if they didn't have order in the last month.

        => For customer  have order in the last month, and current month are also have then called " Repeat".

        => column that we count are InvoiceType "F2" and OrderType "SALE". these column are refer to order and sales, beside it's return.

         

        Sample file https://drive.google.com/drive/folders/1UzgG-CXW3G0diCSfILUYRqXq9piZWZLj?usp=sharing 

        Any suggestion?