Forum Discussion
Calculate Churn Rate From Two Dates
I'm trying to create a churn measure that will be the percentage of claims that are canceled in a selected year. If I selected 2023 as the date, I would want to see that the churn rate is 50%. This means that 1 out of the 4 policies that were active in 2023 ended before the year was over.
| ChannelCategory | PaymentMethod | CurrentSalesAgent | Num_Claims | PolicyStartDate | PolicyEndDate |
| Channel1 | Credit Card | John Doe | 1 | 1/20/2023 | 2/23/2024 |
| Channel2 | Credit Card | Jane Doe | 1 | 9/3/2021 | 10/7/2021 |
| Channel3 | Credit Card | John Doe | 1 | 2/9/2023 | 3/15/2024 |
| Channel1 | Credit Card | Jane Doe | 1 | 9/23/2021 | 10/22/2022 |
| Channel2 | Credit Card | John Doe | 1 | 3/1/2023 | 4/4/2024 |
| Channel3 | VisFin | Jane Doe | 1 | 10/13/2021 | 11/16/2021 |
| Channel1 | Credit Card | John Doe | 1 | 3/21/2023 | 4/24/2023 |
| Channel2 | Check(ACH) | Jane Doe | 2.5 | 11/2/2021 | 12/6/2021 |
| Channel3 | Credit Card | John Doe | 2.5 | 4/10/2023 | 5/14/2023 |
| Channel1 | Credit Card | Jane Doe | 2.5 | 11/22/2021 | 12/26/2021 |
| Channel2 | VisFin | John Doe | 2.5 | 4/30/2023 | 6/3/2023 |
| Channel3 | Credit Card | Jane Doe | 2.5 | 12/12/2021 | 1/15/2022 |
Anonymous , Create an independent date table and use that in the slicer
//Date1 is independent Date table
Active measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Start Date] <=_max && 'Table'[End Date] >=_min))Inactive measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[End Date] <=_max && 'Table'[End Date] >=_min))Now you can have a ratio
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
6 Replies
- amitchandak
Super User
Anonymous , Create an independent date table and use that in the slicer
//Date1 is independent Date table
Active measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Start Date] <=_max && 'Table'[End Date] >=_min))Inactive measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = minx(allselected(Date1),Date1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[End Date] <=_max && 'Table'[End Date] >=_min))Now you can have a ratio
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
- Ashish_Mathur
Hi,
Please explain how you arrived at 25% (based on the sample data that you have shared).
- Ashish_Mathur
- Ashish_Mathur
You are welcome. There is no independent table there - both Tables are related via the Date column. Please study the steps in the Query Editor to see the transofrmation carried out on the Data Table.