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-custo...
Need Help
  • marcorusso's avatar
    marcorusso
    10 years ago

    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]
)
MattAllington's avatar
MattAllington
Community Champion
10 years ago

Yes I know what a SCO is and I know why they exist.  My point is that I think this is causing the issue.  I believe the lost customer formula expects the dim table to have 1 record per customer and not multiple (best guess).  marcorusso would have to tell you for sure.  

 

I am not saying it is right or wrong, but when I create models, I always take the last view of the customer as my dim table.  If I need a historical view of the customer then I assume you can just load a fact table with the historical records for those customers that have changed and use DAX to extract the historical data.  

 

Once again, this is just an assumption and hopefully Marco can give the definitive answer. 

marcorusso's avatar
marcorusso
Most Valuable Professional
10 years ago

If you have an SCD2, instead of

VALUES ( Sales[CustomerNO] )

you should use

CALCULATETABLE ( VALUES ( Customer[CustomerCode] ), Sales )

 

where CustomerCode is the key for the customer that is the same across all the versions (surrogate keys) of the customer itself.

 

Please note this will slow down the performance and it is the reason why we used the column in Sales.

A best practice is to denormalize the customer code in the fact table, just to use that in this calculation.

 

Please note that if you are using Excel 2016 or Power BI Desktop, it is possible to write the same pattern using the new set functions (INTERSECT, EXCEPT, UNION) in a much faster way. We'll update the patterns for these versions, but I'm worried we'll not have time until this autumn.

 

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

    Thank you, Marco. That gives me the correct answer if I only have the Customer No in a pivot table. If I include the Customer Name in the pivot table (this is the attribute that changed over time), it counts them as lost. Is there a way to alter the calculation to change this behavior so I get consistent results in both pivots? 

     

    For example, I have a customer who bought something in December and then their name changed, then they had purchases in January and February. 

      

    Customer 
CustomerKey Customer No Customer Name EffectiveStart EffectiveEnd RowIsCurrent
1           1           Person A      1/1/1900 1     2/31/2015    0
2           1           Person A2     1/1/2016                    1
    SalesInvoice 
InvoiceDateKey InvoiceDate CustomerKey ProductKey SalesAmount
20151218       12/18/2015  1           3          10
20160129       1/29/2016   2           4          15
20160216       2/12/2016   2           5          20

    Their pivot with just Customer No looks good (they are not counted as lost). But if I include Customer Name after the Customer Number, they are counted as lost in February, despite the fact that I am not using the CustomerKey (surrogate key) anywhere in the formula.

     

    • marcorusso's avatar
      marcorusso
      Most Valuable Professional
      10 years ago

      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]
)
      • Meagan's avatar
        Meagan
        Most Valuable Professional
        10 years ago

        Thanks so much, Marco. That did the trick. For anyone else who happens to need it, here is my full DAX calculation: 

        Lost Customers:=IF (
    NOT (
        MIN ( 'Date'[Full Date] )
            > CALCULATE ( MAX ( Sales[Invoice Date] ), ALL ( Sales ) )
    ),
    COUNTROWS (
        FILTER (
            ADDCOLUMNS (
                FILTER (
                    CALCULATETABLE (
                        ADDCOLUMNS (
                            CALCULATETABLE ( VALUES ( Customer[Customer No] ), Sales ),
                            "CustomerLostDate", CALCULATE (
                                MAX ( Sales[Invoice Date] ),
                                ALLEXCEPT ( Customer, Customer[Customer No] )
                            )
                                + [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] )
        )
    )
)