Calculate Churn Rate From Two Dates

[Anonymous]

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

[amitchandak · Accepted solution]

@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

[Anonymous]

This looks great! What is the date range of the independent table? Based on the policyStartdate and policyenddate ranges?

[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.

[Ashish_Mathur]

Hi,

Please explain how you arrived at 25% (based on the sample data that you have shared).

[Anonymous]

Apologies, this should be 50%. 3 of them started and ended in 2023 while another 3 started in 2023 but didn't end until after 2023. It should be 3/6 = 50%

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[amitchandak · Accepted solution]

@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