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('Sales Order'[Customer Code]), 'Sales Order'[InvoiceType] = "F2", 'Sales Order'[OrderType] = "SALE")
 
So now we're looking for New Customer & Repeat Customer that calculation are current month compare to previous month.
 
Example: we're standing in Oct'23, then compare with Sep'23. If customer can see/find in Oct'23 => Repeat. If customer cannot find then it's New. Actually, customer purchased = Repeat + New
 
 
Any suggestion/advise to find it?
 
Thanks and Regards,
Need Help

20 Replies

    • ADSL's avatar
      ADSL
      Post Prodigy
      2 years ago

      Hi Ashish_Mathur,

       

      Thank you for your helpful feedback.

       

      In sales order table, it have for both customer order and return then when we calculate and find the customer purchased, we need to count and filter only for 02 column name 'Sales Order'[InvoiceType] = "F2", 'Sales Order'[OrderType] = "SALE").

       

      If we don't filter these columns then number of customer purchased are more than because it count for customer return too.

       

      So any suggestion/advice of your meause?

       

      Thanks and Regards,

       

      • 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.