Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EiA
Frequent Visitor

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!

1 ACCEPTED SOLUTION
johnt75
Super User
Super User

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

View solution in original post

2 REPLIES 2
EiA
Frequent Visitor

Thanks you @johnt75  - excellent solution!

johnt75
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.