Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am working on creating Customer Churn Rate Measure using the below logic:
For example: we want to compute Churn Rate in the Month of March, 2022.
Churn Calculation:
- There are 10,000 Users who have purchased at least once in last one year (i.e. from Apr 2021 - Mar 2022) – this becomes the ‘User Base’.
- Within the 'User Base', if there are 4,200 users who have placed an order in last 125 days.
- To calculate Churned Users, i.e. users who have not purchased in last 125 days, will be: 10,000 - 4,200 = 5,800. This is the Churned Users count.
- Then Churn Rate is calculated as: (Churned Users/Base of users)% i.e. 5800/10000 = 58%
We have Fiscal Year and Fiscal Month Slicer in the report:
Fiscal Year : 1 Oct 2021 - 30 Sep 2022
Logic I created
For User Base:
First Date: FIRSTDATE(DATESINPERIOD(Orders[order_date],FIRSTDATE(Orders[order_date]),-1,YEAR))
Last Date: LASTDATE(DATESINPERIOD(Orders[order_date],LASTDATE(Orders[order_date]),-1,YEAR))
But if I am selecting more than 1 month in slicer, it is returning the total as latest month data and not the Average of months selected. Please see the below screenshot:
I need the calculation to show total as shown in the below screenshot. Can someone help me here to calculate Cumulative Average?
Solved! Go to Solution.
Hi @Anonymous ,
Please try the following measures:
First Date =
IF (
HASONEVALUE ( Orders[Month] ),
FIRSTDATE (
DATESINPERIOD ( Orders[order_date], FIRSTDATE ( Orders[order_date] ), -1, YEAR )
)
)
Last Date =
IF (
HASONEVALUE ( Orders[Month] ),
LASTDATE (
DATESINPERIOD ( Orders[order_date], LASTDATE ( Orders[order_date] ), -1, YEAR )
)
)
Firstdate_125 =
IF (
HASONEVALUE ( Orders[Month] ),
FIRSTDATE (
DATESINPERIOD ( Orders[order_date], MAX ( Orders[order_date] ), -126, DAY )
)
)
User Base =
AVERAGEX (
VALUES ( Orders[Month] ),
CALCULATE (
DISTINCTCOUNT ( Orders[Users] ),
DATESBETWEEN ( 'Date'[Fiscal Date], [First Date], [Last Date] ),
ALL ( Orders )
)
)
Bought in 125 Days =
AVERAGEX (
VALUES ( Orders[Month] ),
CALCULATE (
DISTINCTCOUNT ( Orders[Users] ),
DATESBETWEEN ( 'Date'[Fiscal Date], [Firstdate_125], [Lastdate_125] ),
ALL ( Orders )
)
)
Churned Users = [User Base] - [Bought in 125 Days]
Churn_rate =
AVERAGEX ( VALUES ( Orders[Month] ), DIVIDE ( [Churned Users], [User Base] ) )
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Please try the following measures:
First Date =
IF (
HASONEVALUE ( Orders[Month] ),
FIRSTDATE (
DATESINPERIOD ( Orders[order_date], FIRSTDATE ( Orders[order_date] ), -1, YEAR )
)
)
Last Date =
IF (
HASONEVALUE ( Orders[Month] ),
LASTDATE (
DATESINPERIOD ( Orders[order_date], LASTDATE ( Orders[order_date] ), -1, YEAR )
)
)
Firstdate_125 =
IF (
HASONEVALUE ( Orders[Month] ),
FIRSTDATE (
DATESINPERIOD ( Orders[order_date], MAX ( Orders[order_date] ), -126, DAY )
)
)
User Base =
AVERAGEX (
VALUES ( Orders[Month] ),
CALCULATE (
DISTINCTCOUNT ( Orders[Users] ),
DATESBETWEEN ( 'Date'[Fiscal Date], [First Date], [Last Date] ),
ALL ( Orders )
)
)
Bought in 125 Days =
AVERAGEX (
VALUES ( Orders[Month] ),
CALCULATE (
DISTINCTCOUNT ( Orders[Users] ),
DATESBETWEEN ( 'Date'[Fiscal Date], [Firstdate_125], [Lastdate_125] ),
ALL ( Orders )
)
)
Churned Users = [User Base] - [Bought in 125 Days]
Churn_rate =
AVERAGEX ( VALUES ( Orders[Month] ), DIVIDE ( [Churned Users], [User Base] ) )
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , refer the formula in these blogs , they can help
Customer Retention Part 5: LTD Vs Period Retention
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-5-LTD-and-PeriodYoY-Retention-is-only/ba-p/2114497
Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |