Calculate count of specific customers who are new, lost, or returning customers

[GZavela]

Hello,

I am trying to figure out how to calculate which of our customers are new customers, lost customers, and returning customers over a period of time.  For example, over the past 4 years I want to see how many customers are new, by year/month, how many customers we have lost, by year/month, and how many are returning by year month.

The criteria is:

New customers - Customers who have not purchased anything in the entirety of the previous year in total (not year/month), but have purchased something in the "current" year/month

Lost customers - Customers who have purchased something in the entirety of the previous year, but not the "current" year/month

Returning customers - Customers who have purchased something for the entirety of the previous year, and the "current" year/month

This would need to be over multiple years, and year/months.  The example below is what the data would look like if a customer has purchased something over a course of 4 year:

Customer	Posting Date	Document No
C10000	1/1/2015	SI000001
C10000	4/1/2015	SI000002
C10000	3/16/2016	SI000003
C10000	1/1/2018	SI000005
C10001	1/1/2016	SI000006
C10001	5/1/2017	SI000007
C10001	12/4/2018	SI000008
C10002	1/5/2018	SI000008
C10003	1/1/2015	SI000009
C10003	4/8/2016	SI000010
C10003	3/15/2017	SI000011
C10003	12/13/2018	SI000012
C10004	1/1/2016	SI000013
C10004	12/4/2018	SI000014

Based on the data above, the customer status would be as described below for the 4 year period of time:

Customer	Customer Status
C10000	New customer for 2015
	Returning customer for 2016
	Lost customer for 2017
	New customer for 2018
C10002	New customer for 2018
C10003	New customer for 2015
	Returning customer for 2016
	Returning customer for 2017
	Returning customer for 2018
C10004	New customer for 2016
	New customer for 2018

Based on the two sets of data above, if I were to create a pivot table it should look like the example below:

Year

Month

2015

2016

2017

2018

Customer Status

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Count of New Customers

2

2

2

1

Count of Lost Customers

Count of Returning Customers

1

1

1

1

2

I am unable to attach the workbook example, so let me know if something does not make sense, or is unclear, and thank you in advance!

[AkhilAshok · Accepted solution]

You can follow the below steps:

1. Create a Date table and add a relationship between Date[Date] & Purchase[Posting Date]. If you don't have one, you can use the sample logic below:

Date = 
VAR BaseCalendar =
    CALENDAR ( MIN(Purchase[Posting Date]), MAX(Purchase[Posting Date]) )
RETURN
    GENERATE (
        BaseCalendar,
        VAR BaseDate = [Date]
        VAR YearDate = YEAR ( BaseDate )
        VAR MonthNumber = MONTH ( BaseDate )
        RETURN ROW (
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", FORMAT ( BaseDate, "mmm" )
        )
    )

2. Create the following measures:

New Customer = 
VAR CurrentYear =
    MAX ( 'Date'[Year] )
VAR CurrentMonth =
    MAX ( 'Date'[Month Number] )
VAR CurrentCustomers =
    CALCULATETABLE ( VALUES ( Purchase[Customer] ), ALL ( 'Date'[Date] ))
VAR PreviousCustomers =
    CALCULATETABLE (
        VALUES ( Purchase[Customer] ),
        FILTER (
            ALL ( 'Date' ),
            'Date'[Year] = CurrentYear - 1
                || ( 'Date'[Year] = CurrentYear
                && 'Date'[Month Number] < CurrentMonth )
        )
    )
VAR NewCustomers =
    EXCEPT ( CurrentCustomers, PreviousCustomers )
RETURN
    COUNTROWS ( NewCustomers )

Lost Customers = 
VAR CurrentYear =
    MAX ( 'Date'[Year] )
VAR CurrentMonth =
    MAX ( 'Date'[Month Number] )
VAR CurrentYearCustomers =
    CALCULATETABLE (
        VALUES ( Purchase[Customer] ),
        ALLEXCEPT ( 'Date', 'Date'[Year] )
    )
VAR PreviousYearCustomers =
    CALCULATETABLE (
        VALUES ( Purchase[Customer] ),
        ALL ( 'Date' ),
        'Date'[Year] = CurrentYear - 1
    )
VAR LostCustomers =
    EXCEPT ( PreviousYearCustomers, CurrentYearCustomers )
VAR IsYearGrain =
    NOT ( ISFILTERED ( 'Date'[Month] ) || ISFILTERED ( 'Date'[Date] ) )
RETURN
    IF ( IsYearGrain, COUNTROWS ( LostCustomers ) )

Returning Customers = 
VAR CurrentYear =
    MAX ( 'Date'[Year] )
VAR CurrentMonth =
    MAX ( 'Date'[Month Number] )
VAR CurrentCustomers =
    CALCULATETABLE ( VALUES ( Purchase[Customer] ), ALL ( 'Date'[Date] ) )
VAR PreviousYearCustomers =
    CALCULATETABLE (
        VALUES ( Purchase[Customer] ),
        ALL ( 'Date' ),
        'Date'[Year] = CurrentYear - 1
    )
VAR ReturningCustomers =
    INTERSECT ( PreviousYearCustomers, CurrentCustomers )
RETURN
    COUNTROWS ( ReturningCustomers )

From you requirement, I see that Lost customers you only want to display at Year grain?

Let me know if this works for you.

[charleshale]

Hi.  I love how elegant the code is...but I'm getting some wonky results.    Note "Card Name" as my field for the unique name.    Otherwise, I'm using 100% the code for the # customers that have transacted in the prior 12 mos, and 99% of the same code for customers that have transacted in the prior month (just adding an additional filter).

/12Mo_RepeatCusts_By#/:=	
VAR CurrentYear =	
    MAX ( 'Date'[Year] )	
VAR CurrentMonth =	
    MAX ( 'Date'[Month Number] )	
VAR CurrentCustomers =	
    CALCULATETABLE ( VALUES ( Table1[Card Name]), ALL ( 'Date'[Date] ) )	
VAR PreviousYearCustomers =	
    CALCULATETABLE (	
        VALUES ( Table1[Card Name] ),	
        ALL ( 'Date' ),	
        'Date'[Year] = CurrentYear - 1	
    )	
VAR ReturningCustomers1Yr =	
    INTERSECT ( PreviousYearCustomers, CurrentCustomers )	
RETURN	
    COUNTROWS ( ReturningCustomers1Yr )	

/1Mo_RepeatCusts_By#/:=			
VAR CurrentYear =			
    MAX ( 'Date'[Year] )			
VAR CurrentMonth =			
    MAX ( 'Date'[Month Number] )			
VAR CurrentCustomers =			
    CALCULATETABLE ( VALUES ( Table1[Card Name]), ALL ( 'Date'[Date] ) )			
VAR PreviousMonthCustomers =			
    CALCULATETABLE (			
        VALUES ( Table1[Card Name] ),			
        ALL ( 'Date' ),			
        	'Date'[Year] = CurrentYear, 		
        	'Date'[Month Number] = CurrentMonth - 1  		
        	  )		
VAR ReturningCustomers1Mo =			
    INTERSECT ( PreviousMonthCustomers, CurrentCustomers )			
RETURN			
    COUNTROWS ( ReturningCustomers1Mo )			

The results are wonky because, for many periods, I get more customer who transacted in the prior month than in the prior year.  Possibly, I have messed up by 1Mo Repeat Customer adaptation.  Could it be, however, that the measures above have a problem because they are absolute rather than relative?  Ie they peg to a year and a prior year whereas the best measure should be 365 days or 30 days?

I apologize for not including my file.   I'm using excel power pivot not PBIX and have some PII in the files

[AkhilAshok]

A sample data which illustrastes the issue would be helpful. Meanwhile, for getting previous month customers a better approach would be following:

VAR PreviousMonthCustomers =
CALCULATETABLE ( VALUES ( Table1[Card Name] ), PREVIOUSMONTH ( 'Date'[Date] ) )