Finding Lost Customers

Kevin Feasel

2016-03-01

DAX

Meagan Longoria has lost customers and is trying to find them:

I was testing some calculations in my tabular model when I noticed that my Lost Customers calculation wasn’t working as expected. This was rather interesting to me since the calculation I was using was from DAXPatterns.com.  After some experimentation, I determined that the difference between my data situation, and the situation that fit the DAX Pattern was that my customer dimension was a Type 2 Slowly Changing Dimension. That meant I couldn’t use the customer key to identify a unique customer, since each customer could have multiple customer keys  (surrogate keys) with the same customer number (business key). Specifically, if a customer made a purchase in December 2015, then changed their name, then made a purchase in January and February, my calculation was counting them as lost because it was not recognizing that the customer with the new name and same customer number was actually the same customer.

She ends up with two solutions, each with different trade-offs.  Knowing as little DAX as I do, looking at two different ways of solving this problem is great because it gives you more insight into language semantics.

Related Posts

RANKX Across Multiple Columns

Jason Baldessari continues a series on RANKX in DAX: What happens when we need to rank using multiple criteria?  In the example below, we are going to look at resellers by name and key in a SalesTerritoryGroup, and we are going to rank them based on the number of items they have sold. Let’s start […]

Read More

Getting Prior Year’s Year-To-Date with DAX

Kasper de Jonge takes a look at how to calculate a prior year’s year-to-date over the same period as the current year: Well maybe.. what happens here is that the DAX engine took the whole date range we have in context and shifts it back 12 months. This means for year 2019 it will use […]

Read More

Categories

March 2016
MTWTFSS
« Feb Apr »
 123456
78910111213
14151617181920
21222324252627
28293031