Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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 ) )
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 ) )
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |