Forum Discussion
Attrition rate
Hi,
I’m new to Power BI. I would really appreciate some help.
I was also wanting to work out the drop out (attrition) rate of my members per month and year as well. I would also like to know the rate of new months per months I have a table as below:
Customer ID, Status, End date, Start date, campaign
I have made the following date table:
The status corresponds to the member’s status e.g. Ended, active, break (taking a break and will contacted at a later date or reinstated)
Date =
ADDCOLUMNS (
CALENDAR (DATE(2000,1,1), DATE(2025,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)
I would like help with working out drop out rate and new member ratio per marketing campaign activity to measure it’s effectiveness.
How can I go about doing this? Thank you
5 Replies
Hi Anonymous,
Please share us your source table structure and some sample data with expected result. So that we can know the right direction and provide some proper solutions.
Thanks,
Xi Jin.Thank you for your response.
I have managed to work it out. My solution is below with the following measures:
DO rate % = [Incountperenddate]/[Incount]
Incount = CALCULATE(COUNT('Norge faddere'[Kundenummer (Kontakt)]))
Incountperenddate = CALCULATE(COUNT('Norge faddere'[Kundenummer (Kontakt)]), Filter ('Norge faddere', 'Norge faddere'[Avslutningsdato]))
Attached is a snapshot of my table (Avslutningsdato means end date. Kundenummer is customer number. Please ask if you require more information. I have another field in the table which is status - which contains active, inactive customers.
The graph attached shows how the drop out rate is decreasing. On the axis I put the start date (another field in the graph) and in the values field of the graph I included the DO rate % to get this outcome. Which turned out to be correct. yay!
Now what I was hoping for help with is actually the my customer retention rate. So the opposite of what i have achieved. These would be active members which do not have an avslutningsdato as they are still active. How would I go about achieving that?
I look forward to hearing from you and hopefully get a resolution. I would be grateful for any assistance you could provide.
Thank you :)Drop out rate graphSnippet of table (not all fields included as I could not fit it in)
Hi Anonymous,
Sorry for delay.
First I'm glad to hear that you have resolved the prior issue.
Then for the new issue: Now what I was hoping for help with is actually the my customer retention rate. So the opposite of what i have achieved. These would be active members which do not have an avslutningsdato as they are still active.
Same rule, please share us more detailed information. If it is possible, please share us your pbix file with One Drive or Google Dirve. If you can't, please attach some sample data which we can copy and paste directly and its corresponding desired result. So that we can understand your requirement more clearly and make some proper tests. :smileyhappy:
Thanks,
Xi Jin.
