Comparing New vs Lost vs Steady Customers over Multiple Years - Customer Churn Examples - Advanced DAX

sam.mckay · March 20, 2018, 8:53pm

Ok so I’ve decided to take a different approach here.

I think the past answer was getting an answer that may be relevant but not so in this particular case.

I think what you’re looking for instead is actually checking which customers purchased something in any particular year (not the beginning of the data) and then seeing if they don’t purchase in that next year.

This initial answer was calculating if any customer who had purchased (at any time historically) stop purchasing at any point in time in the future. That’s why it looked like a cumulative total.

So to get just the change from one year to the next, this is how I solved it.

It’s actually a relatively clean formula I think.

Customer Lost = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
IF( ISBLANK( [Customer Sales TY] ),
    BLANK(),
        COUNTROWS( EXCEPT( CustomerLY, CustomersTY ) ) )

Here are the other formulas

Customer Sales LY = 
COUNTROWS( CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) ))


Customer Sales TY = 
COUNTROWS( VALUES( Invoices[Customer] ) )

I 'll look at your other ones now

sam.mckay · March 20, 2018, 9:48pm

Ok I’ve been playing around with a number of formulas and I think there is much cleaner ways to do this (I’ll have to update some of my own material with these ideas)

See below for some formula that calculate the same thing, but format better.

New Customers Total = 
VAR PriorCustomers = CALCULATETABLE( VALUES( Invoices[Customer] ), 
                        DATESBETWEEN( 'Calendar'[Date], CALCULATE( MIN( 'Calendar'[Date] ), ALL('Calendar' ) ), LASTDATE( 'Calendar'[Date] ) - 365 ))
VAR CustomerTY = VALUES( Invoices[Customer] )

RETURN
COUNTROWS(
    EXCEPT( CustomerTY, PriorCustomers ) )



New Customers Sales = 
VAR PriorCustomers = CALCULATETABLE( VALUES( Invoices[Customer] ), 
                        DATESBETWEEN( 'Calendar'[Date], CALCULATE( MIN( 'Calendar'[Date] ), ALL('Calendar' ) ), LASTDATE( 'Calendar'[Date] ) - 365 ))
VAR CustomerTY = VALUES( Invoices[Customer] )

RETURN
CALCULATE( [Total Sales],
    EXCEPT( CustomerTY, PriorCustomers ) )



Customer Lost = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
IF( ISBLANK( [Customer Sales TY] ),
    BLANK(),
        COUNTROWS( EXCEPT( CustomerLY, CustomersTY ) ) )



Customer Revenue Lost = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
IF( ISBLANK( [Total Sales] ),
    BLANK(),
        CALCULATE( 
            CALCULATE( [Total Sales], SAMEPERIODLASTYEAR( 'Calendar'[Date] ) ),
                EXCEPT( CustomerLY, CustomersTY ) ))



Customers Steady = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
IF( ISBLANK( [Customer Sales TY] ),
    BLANK(),
        COUNTROWS( INTERSECT( CustomerLY, CustomersTY ) ) )



Customers Sales Steady = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
CALCULATE( [Total Sales],
    INTERSECT( CustomerLY, CustomersTY ) )

Give these all a go.

These are crazy formulas! They are so powerful in there simplicity for something quite complex

And I have to go and update all my own materials!

Thanks
Sam

CorvanDalfzen · March 21, 2018, 2:06pm

Thanks Sam, great work, I am very happy with all your formulas and I understand the techniques.

I’ve made a little change for the “lost” formulas:

Customer Lost = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ); SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
IF( ISBLANK( [Customer Sales TY] );
    BLANK();
        COUNTROWS( EXCEPT( CustomerLY; CustomersTY ) ) *-1 )

Customer Revenue Lost =
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ); SAMEPERIODLASTYEAR( ‘Calendar’[Date] ) )

RETURN
IF( ISBLANK( [Total Sales] );
BLANK();
CALCULATE(
CALCULATE( -[Total Sales]; SAMEPERIODLASTYEAR( ‘Calendar’[Date] ) );
EXCEPT( CustomerLY; CustomersTY ) ))

As you can see the “lost”-figures are now negative.

At this point we’ve almost have every figure but I only need to divide the steady customers in the segments Lost - Steady and Won:

Can you help me with these formulas?

Thanks in advance,

Regards,

Cor

sam.mckay · March 21, 2018, 10:43pm

I struggling to really understand what you need based on the image.

Can you give me more color around the logic and lay it out for me.

CorvanDalfzen · March 22, 2018, 9:34am

Okay Sam, let’s clear this up.

See photo below for the starting point.
I’ve used your formulas for this, I’ve made only a little change for “Customer Lost” and for “Customer Revenue Lost”, both are negative now:

At this point I want us to concentrate on the “Customers Steady”.
I want to divide the steady customers in the following segments:

Lost Revenue – Steady Revenue – Won Revenue

2015:
There are 3 Customers Steady (customer 1, 2 and 3).
Customer 1: Revenue 2015 -/- Revenue 2014 = 45 -/- 25 = 20 = Won Revenue
Customer 2: Revenue 2015 -/- Revenue 2014 = 38 -/- 40 = - 2 = Lost Revenue
Customer 3: Revenue 2015 -/- Revenue 2014 = 27 -/- 35 = - 8 = Lost Revenue

180322-2

Check number of Customers:
There were 4 customers at the end of 2014. We lost in 2015 1 customer and we won 4 customers.
At the end of 2015 we have 7 customers (4 -/- 1 +/+ 4). The report gives the right number.

Check amount of Revenue:
The revenue for 2014 was 112, the revenue for 2015 was 275. The positive difference is 163, what’s happened in 2015?

Customer Revenue Lost:
We lost customer 4 in 2016, revenue lost is -12;

Steady customers:
Lost Revenue steady customers = -10 (customers 2 and 3)
Won Revenue steady customers = 20 (customer 1)

New Customers Sales:
The new customers in 2015 were customers 5, 6, 7 and 8, won revenue: 165

Check the amount:
112 -/- 12 -/- 10 +/+ 20 +/+ 165 = 275, the Total Sales in the report is giving the right amount.

I hope you understand now what I want, if not, then I have to look for another way to explain it to you.

Hopefully you can help me with the formulas for the customers steady.

Thanks in advance,

Regards,

Cor

sam.mckay · March 22, 2018, 11:37pm

Ok thanks for the detail on this. Give me a little bit to work through it, as it’s complex.(but I like the challenge)

Thanks

sam.mckay · March 23, 2018, 12:12am

I’m probably going to have to pick this up next week sorry. Very very complex, need time to sit down and work through this (just have other pressing things right now). Could take me a few hours, just can’t commit to this at this second.

Thanks
Sam

sam.mckay · March 23, 2018, 6:48am

Couldn’t get this off my mind. So have been working on it.

Think I might have it actually.

Which comes from these two tables, and the logic works out from my calcs

Here are the formula

Won Sales Steady = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
CALCULATE( [Sales Diff.],
    FILTER( INTERSECT( CustomerLY, CustomersTY ),
        [Sales Diff.] > 0 ) )



Lost Sales Steady = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
CALCULATE( [Sales Diff.],
    FILTER( INTERSECT( CustomerLY, CustomersTY ),
        [Sales Diff.] < 0 ) )



Same Sales Steady = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
CALCULATE( [Sales Diff.],
    FILTER( INTERSECT( CustomerLY, CustomersTY ),
        [Sales Diff.] = 0 ) )

I went through your list. Does this cover off everything now?

sam.mckay · March 23, 2018, 6:49am

Still the customers bit on steady. I’ll have a look at this.

sam.mckay · March 23, 2018, 6:55am

Ok got these as well I believe.

Formulas

Won Customers Steady = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
COUNTROWS(
    FILTER( INTERSECT( CustomerLY, CustomersTY ),
        [Sales Diff.] > 0 ) )



Lost Customers Steady = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
COUNTROWS(
    FILTER( INTERSECT( CustomerLY, CustomersTY ),
        [Sales Diff.] < 0 ) )




Same Customers Steady = 
VAR CustomersTY = VALUES( Invoices[Customer] )
VAR CustomerLY = CALCULATETABLE( VALUES( Invoices[Customer] ), SAMEPERIODLASTYEAR( 'Calendar'[Date] ) )

RETURN
COUNTROWS(
    FILTER( INTERSECT( CustomerLY, CustomersTY ),
        [Sales Diff.] = 0 ) )

I think this is them all. Let me know.

Real challenge this one, but like the application of it.

Sam

CorvanDalfzen · March 23, 2018, 10:27am

Awesome Sam, this is really awesome! This covers everything now.

Next week I will check these formulas with data from 2006 - 2018 and I’m confident that your measures will work with lots of data.

It’s been a pleasure working with you the past few weeks, I’ve learned a lot from you.

I’m looking forward to speak to you for a next challenge.

See you,

With kind regards,

Cor

sam.mckay · March 23, 2018, 8:39pm

Great, good to hear

CorvanDalfzen · March 27, 2018, 2:42pm

Hi Sam, another question

It seems to be that we have returning customers. For example, a customer has received an invoice in 2015, in 2016 there was no invoice and in 2017 there was a new invoice. In 2016 this customer was counted as a lost customer. Can you help me with the formula for this returning customer?

Thanks in advance,

Cor

CorvanDalfzen · March 27, 2018, 7:20pm

I think that the solution is in the Customer Lost-measure. The other formulas seems to be right.

I was thinking about the measure

New Customers Total = 
VAR PriorCustomers = CALCULATETABLE( VALUES( Invoices[Customer] );
                        DATESBETWEEN( 'Calendar'[Date]; CALCULATE( MIN( 'Calendar'[Date] ); ALL('Calendar' ) ); LASTDATE( 'Calendar'[Date] ) - 365 ))
VAR CustomerTY = VALUES( Invoices[Customer] )

RETURN
COUNTROWS(
    EXCEPT( CustomerTY; PriorCustomers ) )

This measure is looking back, can we change the formula for the lost customers in a sort of “reverse new customer?”" With other words, can the formula look forward instead of looking backwards?

Logic: if the customer has an invoice later than the current invoice, don’t count the customer as lost. If the customer hasn’t an invoice later than the current invoice, count the customer as lost in the year after the current year of the last invoice.

sam.mckay · March 28, 2018, 10:37pm

I’ll look into this.

CorvanDalfzen · March 29, 2018, 6:53am

Hi Sam,

I’ve sent you a separate mail with the pbix-file and an Excel-file.

In the meantime I’ve done 3 things:

1. I've added a new invoice for customer 3 (invoice 1027 - 15-08-2017 - Amount 50);
2. I've made a new table:
    Customers First and Last Invoice = 
    ADDCOLUMNS(VALUES(Invoices[Customer]);
        "First Invoice"; CALCULATE(MIN(Invoices[Invoice_Date]));
        "Last Invoice"; CALCULATE(MAX(Invoices[Invoice_Date])))
3. New Measure:
    Active Customers = 
    CALCULATE(COUNTROWS('Customers First and Last Invoice');
        FILTER(VALUES('Customers First and Last Invoice'[First Invoice]);'Customers First and Last Invoice'[First Invoice] <= MAX('Calendar'[Date]) );
        FILTER(VALUES('Customers First and Last Invoice'[Last Invoice]); 'Customers First and Last Invoice'[Last Invoice] >= MIN('Calendar'[Date]) ))

Result:

Churn%20the%20sequel

The customer lost for 2016 is -2 and that isn’t correct, it counts customer 3. It should be -1, only customer 8.

Maybe you can help me with a new measure for Customer Lost?

Thanks in advance,

Regards,

Cor

sam.mckay · March 31, 2018, 12:36am

Cor, will just require more time on this. Currently on easter holiday weekend with limited access to materials. Will come back when can. Thanks

sam.mckay · April 2, 2018, 11:46pm

One things to note about this is that lost customers is only looking back 365 days.

So if a customer bought, in 2015, then didn’t in 2016, they are considered lost for that year. Then if they purchased again in 2017 they would be considered new again.

I can’t remember, but maybe I thought this was what was decided as the cut off for this.

I’m still investigating the additional logic

sam.mckay · April 2, 2018, 11:57pm

Just a thought on this Cor…

Is this really relevant to calculate it like this?

The reason being is as you are moving through time, you have no idea what might happen in the future, so does evaluating what has happened into the future (like the customer has eventually come back) really mean anything.

The customer may come back because you have identified them as lost and followed up. Your saying in this analysis that if they came back you never lost them.

Hopefully this makes sense around what I’m thinking here.

Thanks
Sam

CorvanDalfzen · April 3, 2018, 2:01pm

It’s absolutely making sense, Sam but when I used your formulas of 23rd of March in my example-file it seems to be correct. When I put these formulas in data from 2003 until 2018 I’ve got some differences in the calculations.

The calculation
New Customers Total (old) -/- Lost Customers +/+ New Customers Total = New Customers Total (new)
wasn’t correct.
The reason for this was the return of some customers. We’ve counted them as lost but they returned.

Also the following calculation of the Sales was incorrect:
Total Sales (old) -/- Customer Revenue Lost -/- Lost Sales Steady +/+ Won Sales Steady +/+ New Customer Sales = Total Sales (new)

The reason for this was the same, the difference was the sales of the returning customers.

I’m a Finance-man, is it possible to make the numbers correct? Maybe we have to change some formulas of the 23rd of March? Or do I have to accept the differences?

With kind regards,

Cor