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]
)
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]
)
Anonymous's avatar
Anonymous
Not applicable
4 years ago

This is a great calc. The problem Im having is that its counting a customer as lost that didnt buy for 12 consecutive months more than once in their 5 year history. Only the last 12 months with no sales shoudl be counted as lost. How do I get it to only grab the last purchase date.  Wehn I wabt to displayit by month it shows as lost in prior years. thanks

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

    There is a new version of the pattern that considers new, lost, and recovered customers - it is more flexible and you should be able to adapt it to your requirements:

    New and returning customers – DAX Patterns