Forum Discussion

Meagan's avatar
Meagan
Most Valuable Professional
10 years ago
Solved

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.

  • 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]
    )

20 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion

    I have followed the pattern and it worked for me.  The only obvious thing I see is that you have an extract IF statement at the start. Have you tried it without this?

    • Meagan's avatar
      Meagan
      Most Valuable Professional

      I removed it and got the same results. Thanks for the suggestion, though.

       

      I'm thinking that it has to do with the customer dimension being a type 2 SCD. The Dec sales are on one customer key (surrogate key) and the Jan and Feb sales are on another. I don't really understand how that could affect this since I never use the customer key anywhere, and the customer number (business key) is the same for all the customer keys for that customer. 

      • MattAllington's avatar
        MattAllington
        Community Champion

        So you have 2 IDs for the customer?  That sounds suspicious.  I think (eg I am not 100% sure but give it a try) that your dim table needs to connect to a primary key for the customer, and then use the same primary key for all your calcs.  If I understand you correctly (which I may not be), you are joining on one key (SCD Surrogate) and then doing the calc on a different ID.  If this is what you are doing, I am thinking this could be the culprit.