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

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

sam.mckay · April 4, 2018, 9:40pm

This formula here also looks one year forward also to see if customer came back. But it only looks one year forward.

Does this get you what you need?

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

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

CorvanDalfzen · April 6, 2018, 2:35pm

Hi Sam, thanks for you new formula!

Next week I will try this formula with the data from 2003-2018 and I will let you know what the results are.

Have a nice weekend!

Regards,

Cor

CorvanDalfzen · April 10, 2018, 2:33pm

Hi Sam, your formula is working on the dummy data but, unfortunately, isn’t the solution for the data from 2003 until 2018. I think that I have to accept the differences. There are too many ways we’ve invoiced our customers and it isn’t possible to make different measures for every exception.

I would like to thank you for the many DAX-lessons and I’m looking forward to speak to you for a next challenge.

With kind regards,

Cor

sam.mckay · April 10, 2018, 10:37pm

Ok thanks for letting me know

CorvanDalfzen · July 31, 2018, 11:08am

Hi Sam,

Can you help me with another churn-question…?

Instead of a yearly churn-comparison our company wants a quarterly comparison:

Additional information: this is dummy data en LTM means “Last Twelve Months”.

How do I have to change the formulas for the new - lost and steady customers?

For the totals of the dummy data I have

Total Customers LTM = 
CALCULATE(DISTINCTCOUNT(Invoices[Customer]);
    DATESBETWEEN('Calendar'[Date];
        NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE('Calendar'[Date])));
        LASTDATE('Calendar'[Date])))

With kind regards,

Cor

CorvanDalfzen · July 31, 2018, 11:22am

The dummy data I’ve used:

Customer	Invoice	Invoice_Date	Year_Invoice	Amount_Excl_VAT	YMD-key
1	1001	1-3-2014	2014	25	20140301
2	1002	5-6-2014	2014	40	20140605
3	1003	9-9-2014	2014	35	20140909
4	1004	10-12-2014	2014	12	20141210
1	1005	1-4-2015	2015	45	20150401
2	1006	3-3-2015	2015	38	20150303
3	1007	2-2-2015	2015	27	20150202
5	1008	4-6-2015	2015	30	20150604
6	1009	2-8-2015	2015	60	20150802
7	1010	7-7-2015	2015	40	20150707
8	1011	15-10-2015	2015	35	20151015
1	1012	21-2-2016	2016	73	20160221
2	1013	7-11-2016	2016	45	20161107
5	1014	15-7-2016	2016	60	20160715
6	1015	2-4-2016	2016	60	20160402
7	1016	28-8-2016	2016	38	20160828
9	1017	14-1-2016	2016	10	20160114
10	1018	8-11-2016	2016	15	20161108
11	1019	25-11-2016	2016	27	20161125
1	1020	1-4-2017	2017	98	20170401
5	1021	23-2-2017	2017	58	20170223
7	1022	8-12-2017	2017	36	20171208
9	1023	24-12-2017	2017	20	20171224
10	1024	6-6-2017	2017	12	20170606
11	1025	1-6-2017	2017	27	20170601
12	1026	8-6-2017	2017	25	20170608

sam.mckay · August 2, 2018, 7:04pm

Hi Cor,

Another tough one here.

Can you add the Power BI model here. Just going to require some testing I think.

I just want to be able to see exactly how this is going to be represented and then run some formula testing through it.

Thanks

CorvanDalfzen · August 2, 2018, 7:45pm

bi2-207 churn analysis new-steady-lost customers.pbix (219.3 KB)
Hi Sam,

See attachment for the dummy data.

Chrs,

Cor

sam.mckay · August 6, 2018, 11:42pm

Hi Cor, sorry for the delay

Have you by chance reviewed this resource here?

This has basically the formula pattern to use for quarterly churn calculations

Here’s a summary of it

New Customers 90 days = 
VAR CustomerTM = VALUES( Sales[Customer ID] )
VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer ID] ), 
                        FILTER( ALL( Dates ),
                            Dates[Date] > MIN( Dates[Date] ) - 90 &&
                            Dates[Date] < MIN( Dates[Date] ) ) )

RETURN
COUNTROWS(
    EXCEPT( CustomerTM, PriorCustomers ) )

That’s quite a detailed workshop as well

See here for lost customers as well

Lost Customers = 
VAR CustomersPurchased = CALCULATETABLE( VALUES( Sales[Customer ID] ), 
                            FILTER( ALL( Dates ),
                                Dates[Date] >  MIN( Dates[Date] ) - 365 && 
                                Dates[Date] <=  MIN( Dates[Date] ) - 90 ) )
VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer ID] ), 
                        FILTER( ALL( Dates ),
                            Dates[Date] >  MAX( Dates[Date] ) - 90 &&
                            Dates[Date] <= MAX( Dates[Date] ) ) )
RETURN
COUNTROWS( EXCEPT( CustomersPurchased,  PriorCustomers ) ) * -1

Understanding CALCULATETABLE is key here

See how you go with these

CorvanDalfzen · August 9, 2018, 9:10pm

Hi Sam,

Sorry for the delay, it is holiday-time in The Netherlands and I wasn’t able to access the materials.
I saw both of the videos you’ve mentioned and the techniques are more clear to me than before.
After the holidays I will dive deeper into the quarterly comparisons.

I’ve a question about the new customers and the new customer sales.
The formulas:

New Customers = 
VAR CustomerTM = VALUES( Sales[Customer ID] )
VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer ID] ); 
                    FILTER( ALL( Dates );
                        Dates[Date] > MIN( Dates[Date] ) - [Churn Time Frame Value] &&
                        Dates[Date] < MIN( Dates[Date] ) ) )

RETURN
COUNTROWS(
EXCEPT( CustomerTM; PriorCustomers ) )

New Customer Sales = 
VAR CustomerTM = VALUES( Sales[Customer ID] )
VAR PriorCustomers = CALCULATETABLE( VALUES( Sales[Customer ID] ); 
                        FILTER( ALL( Dates[Date] );
                            Dates[Date] > MIN( Dates[Date] ) - [Churn Time Frame Value] &&
                            Dates[Date] <= MIN( Dates[Date] ) ) )

RETURN
CALCULATE( [Total Sales];
    EXCEPT( CustomerTM; PriorCustomers ) )

In the first you’ve used the part “Dates[Date] < MIN( Dates[Date]”, in the second you’ve used the part “Dates[Date] < = MIN( Dates[Date]”, why did you use in the second the extra “=”?

I will talk to you after the holidays,

Chrs,

Cor

sam.mckay · August 9, 2018, 9:11pm

I actually think the = is a small oversight. Don’t think you need it here.

CorvanDalfzen · August 23, 2018, 8:29pm

Hi Sam,

I’m back from my holiday in Valencia-Spain and I’m struggling again with the formulas so please help me.
I want to compare different periods with each other, see photo below:

How can I put those periods in the different DAX-formulas? I understand the techniques of CALCULATETABLE, EXCEPT, INTERSECT etc. but I have some difficulties to put those periods into CALCULATETABLE.

Thanks in advance,

Cor

sam.mckay · August 24, 2018, 5:08am

Have you got a date table setup?

You will need one for using these formulas and to run this type of analysis.

You can use the date table code within all the course and resources that are made available.

You will just need to make sure your quarters column aligns with the correct dates, then you can just use these formula as is.

Let me know.

Sam

CorvanDalfzen · August 24, 2018, 3:31pm

Yes, Sam, I have a date table but I am struggling with the formula.

Won Customers LTM =
VAR CustomerLTM = CALCULATE(DISTINCTCOUNT(Invoices[Customer]);
FILTER(ALL(‘Calendar’);
** what do I have to put here?**
VAR PriorCustomers = CALCULATE(DISTINCTCOUNT(Invoices[Customer]);
FILTER(ALL(‘Calendar’ );
** what do I have to put here?
RETURN
COUNTROWS(
EXCEPT(CustomerLTM; PriorCustomers ) )
))

Can you help me?

Thanks in advance,

Cor

CorvanDalfzen · April 6, 2020, 7:03am

For the CustomerLTM seems to me that
DATESBETWEEN (
Calendar[FullDate],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( Calendar[FullDate] ) ) ),
LASTDATE ( Calendar[FullDate] )
)
is the part that I missed but what do I have to use for the PriorCustomers?

CorvanDalfzen · August 24, 2018, 9:02pm

I think I’ve found it:

Won Customers LTM = 
VAR CustomerLTM = CALCULATETABLE(VALUES(Invoices[Customer]);
                         DATESBETWEEN ('Calendar'[Date];
                         NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) );
                         LASTDATE ( 'Calendar'[Date] )))
VAR PriorCustomers = CALCULATETABLE(VALUES(Invoices[Customer]);
                       DATESBETWEEN('Calendar'[Date];
                        DATEADD(STARTOFQUARTER('Calendar'[Date]);-4;QUARTER);
                        DATEADD(ENDOFQUARTER('Calendar'[Date]);-1;QUARTER)))              
RETURN
COUNTROWS(
    EXCEPT(CustomerLTM;PriorCustomers))

sam.mckay · August 26, 2018, 11:10pm

Ok yep that’s great. Yes, I think you certainly have the idea now.

Some sort of pattern but just slight adjustment required for the context of the calculation like you have done.

Chrs, Sam

CorvanDalfzen · September 24, 2018, 11:13am

Hi Sam,

180924 YH Churn per month -1 2003-2004.pbix (593.0 KB)

See attachment.

I am looking for a correct formula for Won Clients for 2004-2.
The context is Year-Month and the comparison is the number of customers last 12 months with the number of customers last 12 month for the prior month.

The correct answer for periode 2004-2 is 356 customers and I have made 3 different measures and the results of these 3 measures is 384 customers. I’m doing something wrong but I don’t know what. Maybe is the reason the leapday 29-02? Can you help me with the correct formula?

Another question:
Result 2003-1: the Total Clients Prior Period -1M and Lost Clients LTM give a result. What can I do to get rid of these results?

Chrs, Cor

sam.mckay · September 25, 2018, 2:43am

Check this one out

Won3 Clients LTM = 
VAR CustomerLTM = CALCULATETABLE(VALUES('2003-2004 Invoices'[Customer]),
                    DATESBETWEEN ('Calendar'[Date],
                         NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Calendar'[Date] ) ) ),
                         LASTDATE ( 'Calendar'[Date] )))
VAR PriorCustomers = CALCULATETABLE(VALUES('2003-2004 Invoices'[Customer]),
                       DATESBETWEEN('Calendar'[Date],
                         DATEADD(STARTOFMONTH('Calendar'[Date]),-13,MONTH),
                         LASTDATE( DATEADD('Calendar'[Date],-1,MONTH) )))
RETURN
COUNTROWS(
    EXCEPT(CustomerLTM,PriorCustomers))

The original formula wasn’t actually finding the last date in January, it was going to the 29th

I just tested it (and that’s what I’ve circled). So I knew that wasn’t what you wanted.

You’ll see in the below formula I’ve adjusted it a little bit.

I always break things down like this to audit. It’s the best way.

Chrs

Latest Enterprise DNA Initiatives

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