Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Meagan
Super User
Super User

Lost Customers - Issues with DAX Calculation

I'm having an issue with a DAX calc for lost customers and I'm hoping someone can spot where we went wrong. I have followed the pattern described in http://www.daxpatterns.com/new-and-returning-customers/ to get lost customers, but it is returning unexpected results. I'm hoping there is just a mistake in my formula that I am overlooking. I'm curious if anyone else has tried this and experienced a similar issue.

 

I have tables for Customer, Date, Sales, which are relevant here. Sales are at the invoice line level, and each line is related to customer by CustomerKey (surrogate key to a type 2 SCD) and date through InvoiceDateKey (surrogate key to date dim). The InvoiceDate field was added as a calculated column to the Sales table.

 

We are defining lost customers as any customer who hasn't made a purchase in 60 consecutive days ending in the selected timeframe. I have a customer that had purchases on 18-Dec-15, 29-Jan-16, and 12-Feb-16, but is somehow being counted as a lost customer in February. The only thing I noticed about them is that the Dec sales records are related to a different customer key than the Jan and Feb sales because someone corrected the customer name. I don't think this should happen since I'm counting unique [Customer No] values rather than [CustomerKey] values. 

 

Here's my formula: 

Lost Customers :=
IF (
    NOT (
        MIN ( 'Date'[Full Date] )
            > CALCULATE ( MAX ( Sales[Invoice Date] ), ALL ( Sales ) )
    ),
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                FILTER (
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            VALUES ( Sales[CustomerNO] ),
                            "CustomerLostDate", CALCULATE ( MAX ( Sales[Invoice Date] ) ) + [Lost Days Limit]
                        ),
                        FILTER (
                            ALL ( 'Date' ),
                            AND (
                                'Date'[Full Date] < MIN ( 'Date'[Full Date] ),
                                'Date'[Full Date]
                                    >= MIN ( 'Date'[Full Date] ) - [Lost Days Limit]
                            )
                        )
                    ),
                    AND (
                        AND (
                            [CustomerLostDate] >= MIN ( 'Date'[Full Date] ),
                            [CustomerLostDate] <= MAX ( 'Date'[Full Date] )
                        ),
                        [CustomerLostDate] <= CALCULATE ( MAX ( Sales[Invoice Date] ), ALL ( Sales ) )
                    )
                ),
                "FirstBuyInPeriod", CALCULATE ( MIN ( Sales[Invoice Date] ) )
            ),
            OR ( ISBLANK ( [FirstBuyInPeriod] ), [FirstBuyInPeriod] > [CustomerLostDate] )
        )
    )
)

 

 Any ideas where my issue is? 

 

Edit: 

 

Here are the results in a pivot table. Notice the first pivot has only Customer No and seems to work. The second includes customer name and provides unexpected results.

LostCust.jpg

1 ACCEPTED SOLUTION

Good point - you have to remove some filter from the CustomerLostDate column expression.

Try this one replacing the ADDCOLUMNS in original formula:

ADDCOLUMNS (
    CALCULATETABLE ( VALUES ( Customer[CustomerCode] ), Sales ),
    "CustomerLostDate", CALCULATE (
        MAX ( Sales[Invoice Date] ),
        ALLEXCEPT ( Customer, Customer[CustomerCode] )
    )
        + [Lost Days Limit]
)

View solution in original post

20 REPLIES 20

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.