Forum Discussion

InsightSeeker's avatar
InsightSeeker
Helper III
1 year ago

Customers Based on Order History

I need help identifying and categorizing customers in Power BI based on the selected date using the following conditions:

 

  1. If a customer has not placed an order for more than 3 months as of the selected date, they should be categorized as a Lost Customer.
  2. If a customer has been ordering from us for more than 12 months as of the selected date, they should be categorized as an Existing Customer.
  3. If a customer has been ordering from us for 0 to 12 months as of the selected date, they should be categorized as a New Customer.

How can I achieve this using DAX or any other approach in Power BI? Any guidance or suggestions are appreciated.

 

Attached is the data sample Download Here

Need Help
Show and Tell

5 Replies

  • amustafa's avatar
    amustafa
    Solution Sage
    1 year ago

    I created two DAX calculated columns in table 'data'...

     

    FirstOrderDate = 
CALCULATE(
    MIN('data'[invoice_date]),
    ALLEXCEPT('data', 'data'[customer_1])
)
    LastOrderDate = 
CALCULATE(
    MAX('data'[invoice_date]),
    ALLEXCEPT('data', 'data'[customer_1])
)

     

    Then a DAX measure as following...

     

    CustomerCategory = 
VAR SelectedDate = MAX('Calendar Ultimate'[Date])
VAR LastOrder = CALCULATE(MAX('data'[LastOrderDate]), ALLEXCEPT('data', 'data'[customer_1]))
VAR FirstOrder = CALCULATE(MIN('data'[FirstOrderDate]), ALLEXCEPT('data', 'data'[customer_1]))
VAR MonthsSinceLastOrder = DATEDIFF(LastOrder, SelectedDate, MONTH)
VAR MonthsSinceFirstOrder = DATEDIFF(FirstOrder, SelectedDate, MONTH)
RETURN
    IF(
        MonthsSinceLastOrder > 3, 
        "Lost Customer",
        IF(
            MonthsSinceFirstOrder > 12, 
            "Existing Customer", 
            "New Customer"
        )
    )

     

    Results looks something like this...

     

     

    • InsightSeeker's avatar
      InsightSeeker
      Helper III
      1 year ago

      Hi amustafa - The results at the group level are not displaying correctly in the table, whereas at the customer number (customer_1) level, the results are accurate. How can I ensure that the correct results are displayed at the group level as well?

      Expected behavior:

      • If all entries for a group company are marked as "Lost," the group status should be "Lost."
      • If some entries for a group company are "Lost" and the remaining are "Existing," the group status should be "Existing."
      • If some entries for a group company are "New" and the remaining are "Existing," the group status should still be "Existing."

      How can I achieve this logic in my table?

       

       

  • Rupak_bi's avatar
    Rupak_bi
    Super User
    1 year ago

    Hi InsightSeeker 

     

    Here is your solution in a matrix table. Ensure to break the date relationship between calender and data table.

     

    status =
    var selected_month = [Last Date in selected Month]
    var last_invoice_date = CALCULATE(max(data[invoice_date]),ALLEXCEPT(Customer,Customer[Company_Group]),data[invoice_date]<=selected_month)
    var first_invoice_date = CALCULATE(min(data[invoice_date]),ALLEXCEPT(Customer,Customer[Company_Group]),data[invoice_date]<=selected_month)
    var differance = DATEDIFF(last_invoice_date,selected_month,MONTH)
    var Diff_initial_date = DATEDIFF(first_invoice_date,selected_month,MONTH)
    return
    switch(TRUE(),differance>3,"Lost Customer", differance<=3&&Diff_initial_date>=12,"Existing Customer",differance<=3&&Diff_initial_date<12,"New Customer","NA")

     

    • InsightSeeker's avatar
      InsightSeeker
      Helper III
      1 year ago

      Hi Rupak_bi  - I cannot break the date relationship between the calendar and data table, as my other tables rely on this relationship as well.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi,Rupak_bi and amustafa ,thanks for your concern about this issue.


    Hello,InsightSeeker .I am glad to help you.
    Has your problem been solved?
    If you have found suitable solutions, please share them as it will help more users with similar problems.
    For example, relevant code or links to articles.
    Or you can mark the valid suggestions provided by other users as solutions.

    By the way, using ISINSCOPE() in the matrix allows you to determine the hierarchy in order to change the output in the MEASUREMENT (for the parent hierarchy you want to change the output)
    ISINSCOPE function (DAX) - DAX | Microsoft Learn

    Thank you very much for your understanding and support of Power BI.
    I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
    Best Regards,
    Carson Jian