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,

     

    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.

       

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