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:   If a customer has not placed an order for more than 3 months as of the s...
Need Help
Show and Tell
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?