topic Re: Cumulative Calculation help needed for Churned Customer Rate in DAX Commands and Tips

Cumulative Calculation help needed for Churned Customer Rate

Anonymous — Thu, 28 Apr 2022 15:15:52 GMT

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))

User Base =CALCULATE(DISTINCTCOUNT(Orders[Users]),DATESBETWEEN('Date'[Fiscal Date],[Firstdate],[Lastdate]))

For Bought in 125 Days:

Firstdate_125 = FIRSTDATE(DATESINPERIOD(Orders[order_date],max(Orders[order_date]),-126,day))

Lastdate_125 = LastDATE(DATESINPERIOD(Orders[order_date],MAX(Orders[order_date]),-125,day))

Bought in 125 Days = CALCULATE (DISTINCTCOUNT(Orders[Users]),DATESBETWEEN('Date'[Fiscal Date],[Firstdate_125],Lastdate_125]))

Churned Users = [User Base] - [Bought in 125 Days]

Churn_rate = DIVIDE([Churned Users],[User Base])

I am getting the results right on selecting one Fiscal Month for eg March 2022

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?

Re: Cumulative Calculation help needed for Churned Customer Rate

amitchandak — Thu, 28 Apr 2022 16:08:44 GMT

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

Re: Cumulative Calculation help needed for Churned Customer Rate

v-kkf-msft — Tue, 03 May 2022 06:55:20 GMT

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.