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
Ashish_Mathur's avatar
Ashish_Mathur
Super User
2 years ago

Hi,

Revise my measures to:

New customers = CALCULATE([Customer Purchased],FILTER(VALUES('Sales Order'[Customer Code]),[Date of first interaction]>=MIN('Calendar'[Date])))
Repeat customers = CALCULATE([Customer Purchased],FILTER(VALUES('Sales Order'[Customer Code]),[Date of first interaction]<MIN('Calendar'[Date])))

I do not see any change in the answer though.

 

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.