Forum Discussion

Martijn1204's avatar
Martijn1204
Frequent Visitor
3 years ago
Solved

Defining new customer

Hi all,

 

I am a bit stuck with a difficult DAX function and I just can't get my head around it so maybe you guys can help me.

 

I want to define new customers and old customers, starting from the year 2020.

New customer = When a customer is in the first 12 months of its first purchase. So, customer buys in January 2020 and in the following 12 months this customer is categorized as a "new customer"

Old customer = When a customer is still making purchases after the 12 months prior.

 

I already made the following command but this is only categorizing the new customers as customers who haven't bought anything in the months prior. However, when they did their first purchase in January, they are not a new customer in the following months.

 

Newly customers =

VAR currentcustomers = VALUES(ClientRevenueProfitByService_v5[ClientNameConsolidated])
VAR currentdate = MIN('Date'[Date])
VAR pastcustomers = CALCULATETABLE(VALUES(ClientRevenueProfitByService_v5[ClientNameConsolidated]),ALL('Date'[Date].[Month],'Date'[Date].[MonthNo],'Date'[Date].[Year]),'Date'[Date] < currentdate)
VAR NEWcustomers = EXCEPT(currentcustomers, pastcustomers)

Return COUNTROWS(Newcustomers)

 

I hope you guys can help me on this

8 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    3 years ago

    The definitions are flawed.

     

    I want to define new customers and old customers, starting from the year 2020.

    New customer = When a customer is in the first 12 months of its first purchase. So, customer buys in January 2020 and in the following 12 months this customer is categorized as a "new customer"

    Old customer = When a customer is still making purchases after the 12 months prior.

    When a customer made their first purchase on day A, month B, then regardless of what the customer had been doing in the next 11 months, they are classified as "new" in the very next 11 months (plus the month on which they made their first purchase, of course). What happens after the 11th month after the purchase? What is the classification? Does it depend on whether the customer has made a purchase after the 11th month or not? If they did, then they are "old" and if not, they're still "new" or already "old"? It's not clear.

    • Martijn1204's avatar
      Martijn1204
      Frequent Visitor
      3 years ago

      Hi,

      After the 11th months after their first purchase they move to the category "Old customers". It does not matter if they made a purchase after the 11 months. A new customer moves to the category "Old customer" after 11 months no matter what. If they bought something in the 10th month, they are categorised as "new customer", and if the next time that they buy something is 3 months after the 10th month, they are categorised as "old customer".

       

      Hope this makes it more clear.  

      • daXtreme's avatar
        daXtreme
        Solution Sage
        3 years ago

        Yeah... I think I now do understand the logic. Thanks for clarification. By the way, is it possible for you to give us some kind of data representative of your problem to work with? If so, please place a link to the file here.

    • Martijn1204's avatar
      Martijn1204
      Frequent Visitor
      3 years ago

      Hi,

       

      I also found this article but I can't find that they define "new customers" as customers who purchased something in the first 12 months of their purchase. I now how to define "new customers" as customers who purchased something for the first time in month A. And when it is month B, these customers aren't defined as "new customers".

  • v-jialluo-msft's avatar
    v-jialluo-msft
    Community Support
    3 years ago

    Hi, Martijn1204 ;
    Please follow these steps:
    (1)This is my test data :

    (2) Create a measure to count the new customer

     

    CountNewCustomer = 
VAR _old =
    SUMMARIZE (
        FILTER (
            'ClientRevenueProfitByService_v5',
            'ClientRevenueProfitByService_v5'[Date] <= EDATE ( TODAY (), -12 )
                && 'ClientRevenueProfitByService_v5'[Sales] <> BLANK ()
        ),
        'ClientRevenueProfitByService_v5'[ClientNameConsolidated]
    )
VAR _new =
    SUMMARIZE (
        FILTER (
            'ClientRevenueProfitByService_v5',
            'ClientRevenueProfitByService_v5'[Date] > EDATE ( TODAY (), -12 )
        ),
        'ClientRevenueProfitByService_v5'[ClientNameConsolidated]
    )
RETURN
    COUNTROWS ( EXCEPT ( _new, _old ) )

     

    (3) The result is as follows :

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.
    Best Regards,
    Gallen Luo
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.