Churn calculation

[EiA]

Hi all,

There is obviously already tons of different churn-threads in here, but I wasn't able to find one for my case (may very well be me not knowing what to search for..)

Simplified, but the structure of my data is like this:

ContractID	CustomerID	StartDate	EndDate
1	1	01.01.2022	31.12.22
2	2	01.01.2022	31.12.22
3	3	01.01.2022	31.12.22
4	3	01.01.2023	31.12.23

ContractID 1 and 2 should be considered as churn on 31.12.22.

ContractID 3 should not be considered as churn on the 31.12.22, as the same customer has a new contract starting the day after.

Any advice on how to calculate this or pointers to existing threads already covering it?

Thanks!

[EiA]

Thanks you @johnt75  - excellent solution!

[johnt75 · Accepted solution]

You could create a calculated column like

Has further contract = 
var currentEndDate = 'Table'[EndDate]
return NOT ISEMPTY( CALCULATETABLE( 'Table', ALLEXCEPT('Table', 'Table'[CustomerID]), 'Table'[StartDate] >= currentEndDate ) )