Churn Rate - lost Customer

[Anonymous]

HI,

I want to have the Prior to Last Month Customers and compare that to the last month Customers to  see if those exist in the last month than calculate the churn rate

For example :-

My Prior last month customer = 1000,

and out of 1000 , only 912 were in Last month ( so 88 got lost)

so %Churn rate would be = (1000-912)/1000 )= 0.088= 8.8%

note- 

The lost customer can’t include new customer in that period.

I am using the fiscal calendar.

Calculating the Prior last month  and last month by using this formula 

Prior last month Cust =

TOTALMTD([Disnt Customer],DATEADD(Table[Date],-2,MONTH))

last month Cust =

TOTALMTD([Disnt Customer],DATEADD(Table[Date],-1,MONTH))

Any help in calculating the % churn rate?

Thanks,

Tejaswi

[v-lionel-msft]

Hi @Anonymous ,

I have understood what you want , but I need a sample data model.

Please give me the relevant sample table data and explain the relationship between the tables.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks Lionel for your reply!

Please see attched pbix sample file.

Also attaching the excel spreadsheet for more detailed information. 

Appreaciate your reply!

Thanks,

TD

[Anonymous]

@v-lionel-msft  Missed the sample file.

[Anonymous]

Hi @v-lionel-msft  ,

Did you get a chance to look at it?

Appreciate your help!

Thanks,

Tejaswi

[harshnathani]

HI @Anonymous ,

Pls see if this is what you need.

Last month Cust =

CALCULATE([Distinct Cust], FILTER(ALL('Fiscal Calendar'),'Fiscal Calendar'[Moth1] = MIN('Fiscal Calendar'[Moth1]) - 1 ))

Prior last month Cust =

CALCULATE([Distinct Cust], FILTER(ALL('Fiscal Calendar'),'Fiscal Calendar'[Moth1] = MIN('Fiscal Calendar'[Moth1]) -2 ))

Churn% = DIVIDE([Prior last month Cust],[Last month Cust])

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[Anonymous]

HI @harshnathani 

Thanks Fr your reply!

Unfortunately this is not what I am lookign for.

Did you see my sample .pbix file, and also Spreadsheet attached . I have my relationships shown in the file and also in spreadsheet i have given the detailed explaination.

Please let me know for any additional info.

Thanks,

TD

[Anonymous]

@amitchandak  May be you can help me with this request.

Appreaciate it.

Thanks,

Tejaswi

[amitchandak · Accepted solution]

@Anonymous , The number distinct customer not matching in excel and pbix.

Anyways, I have calculated the same customer and lost a customer. With that you can get the churn

File is attached.  Added customer table and mark Calendar table as Date table.

[MAAbdullah_47]

Hi @amitchandak 
how we calculate the churn rate for lost and new customers as it is introduced on this post :
https://www.daxpatterns.com/new-and-returning-customers/
Thank you