Hi All,
I trying to calculate churn rate for the customers. I am using below query in SQL to get lost and prior customers for last 12 trailing months and sum to eventually get the ratio. Here is query:
select (SUM(lost)*1.00) / SUM(prior) AS ratio
FROM
( SELECT DATEADD(m,1,a.month) AS month,
count(distinct a.id) prior,
0 lost
FROM Test a
WHERE a.month BETWEEN DATEADD(Year,-1,DATEADD(MM,-1,DATEADD(m, DATEDIFF(m, 0, GETDATE()-1), 0))) AND DATEADD(MM,-1,DATEADD(m, DATEDIFF(m, 0, GETDATE()-1), 0))
GROUP BY a.month
UNION ALL
SELECT DATEADD(m,1,a.month) AS month,
0 prior,
count(distinct a.id) lost
FROM Test a
WHERE a.month BETWEEN DATEADD(Year,-1,DATEADD(MM,-1,DATEADD(m, DATEDIFF(m, 0, GETDATE()-1), 0))) AND DATEADD(MM,-1,DATEADD(m, DATEDIFF(m, 0, GETDATE()-1), 0))
AND NOT EXISTS (SELECT ''
FROM Test b
WHERE a.id = b.id
AND b.month = DATEADD(m,1,a.month))
GROUP BY a.month
) x
WHERE x.month < DATEADD(m, DATEDIFF(m, 0, GETDATE()-1), 0)
The inner query gives me data in the below format:
Month Prior Lost
3/1/2020 99 0
4/1/2020 98 0
5/1/2020 100 0
6/1/2020 101 0
7/1/2020 111 0
8/1/2020 123 0
9/1/2020 112 0
10/1/2020 99 0
11/1/2020 96 0
12/1/2020 95 0
1/1/2021 89 0
2/1/2021 99 0
3/1/2021 97 0
3/1/2020 0 8
4/1/2020 0 7
5/1/2020 0 9
6/1/2020 0 8
7/1/2020 0 6
8/1/2020 0 7
9/1/2020 0 8
10/1/2020 0 9
11/1/2020 0 7
12/1/2020 0 9
1/1/2021 0 9
2/1/2021 0 8
3/1/2021 0 10
Finally summing and dividing, gives the churn rate to be 8%. I am trying to replicate this in DAX. I have tried several different approach but no luck. I was able to create the two summarized dataset for prior & lost but not sure how to union or combine them. Any pointers on how to translate this to DAX will be super helpful. Thank you for your time.
