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

Slowly changing dimensions (SCDs) are a common data warehousing practice used to capture history of what an entity looked like at a particular time.  http://www.kimballgroup.com/2008/09/slowly-changing-dimensions-part-2/

 

I cannot connect to just the customer number (the natural/business key) because there are multiple rows in my customer table that have this key. The customer table is tied to the sales fact table via the surrogate key (an autoincremented number that provides no real value other than to uniquely identify a row and serve as a primary key for the dimension table). So I cannot simply count customer keys because that would result in incorrect numbers. To count the number of lost/recovered/new/any customers, I must do a distinct count on the customer number, which would normally be the same as the customer key if I weren't keeping history (and is the same in the DAX pattern).   

 

So basically, I'm trying to figure out how to alter the pattern to work with a slowly changing dimension. 

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.