Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SR1122
Frequent Visitor

Churn Customer Analysis Monthly and Quarterly

I am doing a churn analysis for a project, at the moment I do have the measures and logic to calculate monthly churn and flag customers but I am trying to change the logic to 3 months instead of one month.

The current logic is - if the customer has a transaction last month but doesn't have a transaction this month then it is a churn customer.

New Logic should be - if the customer doesn't have any transactions in the last 3 months then it is a churn customer.  

 

I have the following measure.

 

$Total Customers = DISTINCTCOUNT('Sales'[Companyid])

$MTD = CALCULATE([$Total Customers], DATESMTD('Date'[Date])) $LMTD = CALCULATE([$Total Customers], DATESMTD((DATEADD('Date'[Date],-1, MONTH))))

$New Customers = SUMX(VALUES('Company'[Company ID]), if(ISBLANK([$LMTD]) && not(ISBLANK([$MTD])), 1, BLANK()))

$Churn Customers = SUMX(VALUES(Company[Company ID]), if(ISBLANK([$MTD]) && not(ISBLANK([$LMTD])), 1, BLANK()))

 

 

7 REPLIES 7
Ashish_Mathur
Super User
Super User

Hi,

Create slicers for Year and Month Name from the Date Table and select a Year and Month name.  Write these measure

Revenue earned in 3 months ended the selected month = calculate([$Total Customers],datesbetween('Date'[Date],edate(min('Date'[Date]),-2),max('Date'[Date])))

 

Churned customers = countrows(filter(calculatetable(values(Company[Company ID]),datesbetween('Date'[Date],edate(min('Date'[Date]),-2),max('Date'[Date]))),[Revenue earned in 3 months ended the selected month]=0))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

All the returned values are blank. 

Hi,

Share the download link of the PBI file.  Remove column/tables that are not required for solving the question.  Ensure that my suggested formulas are written there.  I'll try to identify and correct for the mistake.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Here is the link to the file. The logic is the if the customer doesn't have a transaction in the last three months then it is a churn customer. I am trying to create a table with year and month then churn customer count the measure need to work dynamically based to count the churn based on the given month, hopefully that make sense. 

 

https://file.io/rhZdS5AHUXiW

There is no file there.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yueyunzh-msft
Community Support
Community Support

Hi , @SR1122 

According to your description, you want to realize "customer doesn't have any transactions in the last 3 months then it is a churn customer.".

I check your current DAX code , i think you need to modify  the conditions for determining whether it is a churn customer.

For your current need , you need to get the  last 3 months, you can use the EOMONTH() with TODAY() function to get the Date interval and then you can get the coresponding [$Total Customers] if the customer has .

 

For more information, you can refer to :
EOMONTH Function DAX - SqlSkull

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

Can you please provide the complete measures, for me it is not working. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.