Cumulative Calculation help needed for Churned Customer Rate

[Anonymous]

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?

[v-kkf-msft · Accepted 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.

[amitchandak]

@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