Customers Based on Order History

[InsightSeeker]

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

[v-jtian-msft]

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

[Rupak_bi]

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]

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.

[amustafa]

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]

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?