Churn Customer Analysis Monthly and Quarterly

[SR1122]

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

[Ashish_Mathur]

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.

[SR1122]

All the returned values are blank. 

[Ashish_Mathur]

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.

[SR1122]

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

[Ashish_Mathur]

There is no file there.

[v-yueyunzh-msft]

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

[SR1122]

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