Forum Discussion
Lost Customers - Issues with DAX Calculation
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] )
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?
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.
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.
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.
