Determining lost or acquired customers in DAX

I was asked recently how to determine which customers where acquired or lost from last month to the current month.  Here is a small sample of what a fact table would look like, registering customers that ordered over time:

image

Three interesting questions that can be asked are:

  • What is the amount of returning customers?
  • Which customers where added on the last month?
  • Which customers where lost on the last month?

We can solve these questions by extending the pattern outlined by Chris Webb here.  But before we apply the pattern, it is necessary to generate a real date column.  This can be easily done by creating a calculated column with the following simple formula

=[Month]

after which we change the data type to ‘date’, which in effect converts the text value into a date field:

image

We can now apply a similar pattern that one Chris describes:

Returning Customers:=IF( HASONEVALUE( Table1[Date]  )  , IF( DATEADD( VALUES( Table1[Date]), -1, MONTH ) <> BLANK(),
    COUNTROWS(
        CALCULATETABLE(
            DISTINCT( Table1[Customer Name] ),
            CALCULATETABLE( DISTINCT( Table1[Customer Name]  ),
                DATESBETWEEN( Table1[Date], BLANK(),
                    DATEADD( VALUES( Table1[Date]), -1, MONTH )
                    )           
                , ALL(Table1) )
            )
    )
) )

The result is a DAX measure that will give us the accurate count of customers that had purchased in the past and the have decided to buy from us again (Mary Evans and John Rogers):

image

This expression stacks two filtered tables comprising two different contexts:  the expression highlighted in red gives us the current customers,  the expression highlighted in blue gives us the list of customers for the past month.  In DAX, the parameters used in a CALCULATE or CALCULATETABLE statement are then intersected and the resulting output are the common rows across both filtered tables.

Lets move on now to the question of how to determine which customers where acquired this month.  We can use a similar technique but rather than leveraging the default intersect behavior when modifying the filter context via CALCULATE or CALCUALTETABLE, we must now generate an except condition between two sets of data:   we want to count all customers that purchased this month except the ones that had purchases last month:

Acquired Customers:=IF(HASONEVALUE( Table1[Month] ),
    IF( DATEADD( VALUES(Table1[Date]) , -1, MONTH) <> BLANK(), 
    CALCULATE(
        COUNTROWS(VALUES( Table1[Customer Name] ) ),  
           FILTER(
                VALUES(Table1[Customer Name]), 
                    Table1[Customer Name] <>
                    CALCULATETABLE ( VALUES( Table1[Customer Name] ),
                        FILTER( all(Table1[date]), Table1[Date] =
                            DATEADD( VALUES(Table1[Date]) , -1, MONTH)
                        ), ALL(Table1[Month]) 
                    )         
                )

            )            
        )
    )

It is normally assumed we use a single value when passing parameters to the FILTER, however, “except” conditions can be generated by comparing two filtered tables as well. The result is the following:

image

Notice that [Returning Customers] + [Acquired Customers] = [Total Customers].  So why not just subtract the total amount of distinct customers from the [Returning Customers] measure to the [Acquired Customers] value? I would say most of the times that what you need to do. However, what if we want to see exactly who those new customers where?  The expression above can be translated into a DAX query, and whether we use SSMS, DAX Studio or a reporting tool like SSRS, we can then visualize the exact customers that made up that count:

image 

resultset viewed in DAX Studio

Now lets solve the final question.  For this we use the same technique as the one outlined above, but reversing the parameters:  we want to know which customers had sales last month except the ones that purchased this month. This will give us the count of lost customers:

Lost Customers:=IF(
    HASONEVALUE( Table1[Date] )   ,
    COUNTROWS ( FILTER ( CALCULATETABLE ( VALUES( Table1[Customer Name] ),
                FILTER( ALL(Table1[date]), Table1[Date] =
                DATEADD( VALUES( Table1[Date] ), -1, MONTH )
                ), ALL(Table1[Month]) 
                ) ,
            Table1[Customer Name] <>            
            CALCULATETABLE( VALUES ( Table1[Customer Name]  ) )           
        )
  )
)

the result is the following:

image

Here, again, we can view the exacts customers we lost through a modified version of the measure above applied as a DAX query, which could serve as a drilldown view of the totals seen above:

image

resultset viewed in DAX Studio

About these ads

8 Responses to Determining lost or acquired customers in DAX

  1. javierguillen says:

    Hope it helps SF! :)

  2. Javier,

    I am using a variant of your formula in PowerPivot to get the list of customers who have been added or deleted. The formula looks like this for the Added measure:

    Added:=CALCULATE( COUNTROWS( VALUES( Table1[CustomerID] )), FILTER( VALUES( Table1[CustomerID] ), Table1[CustomerID] CALCULATETABLE( VALUES( Table1[CustomerID] ), PREVIOUSMONTH(Table1[SalesDate]) ) ) )

    All works fine in the pivot table so long as I stick to using the ID field in the rows area. If I add other fields to the display, I get records that should not be included.

    Using the data from your table for an example, I added an ID field and changed Mary Evans name in March to Mary Evans-Jones. The data look like this:

    CustomerID,CustomerName,SalesDate,OrderAmount
    1,Carl Simon,2/1/2012,100
    2,John Rogers,2/1/2012,45
    3,Mary Evans,2/1/2012,60
    4,Peter Stafford,2/1/2012,30
    2,John Rogers,3/1/2012,105
    3,Mary Evans-Jones,3/1/2012,110
    5,Sally Gonzales,3/1/2012,70
    6,Javier Guillen,4/1/2012,10

    I create a pivot table, add a slicer, and select March. With just CustomerID in the rows area and the Added measure in values, the results are as expected–the customer ID for Sally Gonzales, and only Sally Gonzales, is returned.

    CustomerID, Added
    5, 1
    Grand total 1

    Now I add CustomerName to the rows area and the results look like this:

    CustomerID, CustomerName, Added
    3, Mary Evans-Jones, 1
    5, Sally Gonzales, 1
    5 Total, 1
    Grand Total, 1

    This is not what I expected at all. Mary Evan-Jones is added to the table, even though she was a customer in the previous month, the only difference being that her name has changed in the current month. If I am using CustomerID in the FILTER clause, how is it that the CustomerName column affects the results?

    The other issue is that PowerPivot lists Mary Evans-Jones’ record in the results, but does not include it in the total. While the total is correct, there is only one added record in March, looking at the details is misleading. It is also interesting that the grand total does not include Mary Evans-Jones either.

    Clearly, my variant of your formula is not cutting it. I suppose this is an issue with context and CALCULATE. If you could provide some insight, I would appreciate it.

    Thanks,
    SF

    • javierguillen says:

      Hi SF

      When you execute the CALCULATETABLE, it is only able to relate to the context of CustomerID. In other words, there are no ‘surviving’ rows when you try to compute the prior month’s values using the context of current CustomerID, prior CustomerID *AND* current CustomerName. The following calculation should give you what you need:


      CALCULATE(
      COUNTROWS( VALUES( Table1[CustomerID] ) ),
      FILTER(
      VALUES( Table1[CustomerID] ),
      Table1[CustomerID] [..not equal to symbol..] CALCULATETABLE(
      VALUES( Table1[CustomerID] ),
      PREVIOUSMONTH(Table1[SalesDate] ) ,
      ALLEXCEPT(Table1, Table1[CustomerID] )
      )
      )
      )

      Notice the addition of the ALLEXCEPT function to the CALCULATETABLE. That will allow context to ignore the customer name or any other attribute you slice with and retain the current value for CustomerID.

  3. Pingback: Genius Required!!!

  4. sunnysunflowers7 says:

    Javier,

    Everything is working great for the added customers, but I am still having trouble with the deleted ones. So long as I just have ID in the rows, the count for and the ID’s of the lost customers is correct. Once I bring in other fields, then I start running into problems with PowerPivot showing records of continuing customers with changed information in name, office, or some other field that I am displaying in the table.

    Here is the measure I am using:
    LostCust:=Countrows( calculatetable( filter( calculatetable( values(Table1[ID]), PREVIOUSMONTH( Dates[Date] ) ), Table1[ID] calculatetable( values(Table1[ID]) ))))

    I have tried all kinds of variations with ALL, ALLEXCEPT, CALCULATETABLE, and FILTER, but I have not been able to make it work. I only want the ID field to be considered when determining whether a customer is lost; however, I want to be able to display a number of different fields in PowerPivot for these lost customers.

    Any tips on this one?

    Thanks,
    SF

  5. Pingback: Another Post about Calculating New and Returning Customers | Gerhard Brueckl's BI Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 72 other followers

%d bloggers like this: