Forum Discussion

EiA's avatar
EiA
Frequent Visitor
3 years ago
Solved

Churn calculation

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:

 

ContractIDCustomerIDStartDateEndDate
1101.01.202231.12.22
2201.01.202231.12.22
3301.01.202231.12.22
4301.01.202331.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!

Need Help
  • johnt75's avatar
    johnt75
    3 years ago

    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 ) )

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    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 ) )