Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Solved! Go to Solution.
@v-huizhn-msft;
I solved my issue in the meantime by disregarding the suggested Churn-rate measure and followed another approach, which was more simple but still correct.
Used the following measure, which is in Danish:
Churn rate (Måned) = VAR AktiveSidsteMaaned = CALCULATE('Kunde Churn'[Total aktive kunder]; Dateadd(Periode[Dato];-1;MONTH)) RETURN VAR DageIMaaned = CALCULATE(COUNTROWS(Periode);VALUES(Periode[Måned])) RETURN VAR NyeKunderIMaaned = [Total aktive kunder] - AktiveSidsteMaaned RETURN VAR KundeDageImaaned = ((AktiveSidsteMaaned * DageIMaaned) + (0,5*NyeKunderIMaaned*DageIMaaned)) RETURN CALCULATE( DageIMaaned * DIVIDE('Kunde Churn'[Churned Kunder];KundeDageImaaned) )
If I understand that formula correctly, you need to have the number of customers who churned on any particular day and the number of customers on that particular day. You then divide those numbers and multiply by thirty.
So, what format is your data in? Do you have it summarized by day the number of customers and churns? Some sample data would help tremendously, even if just mocked up and simplified.
Hi,
I’m new to Power BI
I was also wanting to work out the drop out 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 to help with create measures as well for 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 🙂
Recarding the "total amount of customers" measure, I made this measure based off "Dax Patterns - Time Patterns": http://www.daxpatterns.com/time-patterns/
Total active customers = CALCULATE( CALCULATE( DISTINCTCOUNT(Sales[CustomerKey]); FILTER( ALL(Period[Date]); Period[Date] > MAX(Period[Date]) - 'Customer Churn'[Churn Days] && Period[Date] <= MAX(Period[Date]) ) ); FILTER(Period; Periode[Dat] <= MAX(Sales[InVoiceDate]) ) )
The 'Customer Churn'[Churn Days] refers to another measure that holds a table that is used to filter the amount of days the users wants to use as our "Churn Definition".
Was wondering if you can confirm it's correct @Greg_Deckler ?
Hi @Anonymous,
Could you please sahre some your sample data for further analysis?
Best Regards,
Angelia
@v-huizhn-msft;
I solved my issue in the meantime by disregarding the suggested Churn-rate measure and followed another approach, which was more simple but still correct.
Used the following measure, which is in Danish:
Churn rate (Måned) = VAR AktiveSidsteMaaned = CALCULATE('Kunde Churn'[Total aktive kunder]; Dateadd(Periode[Dato];-1;MONTH)) RETURN VAR DageIMaaned = CALCULATE(COUNTROWS(Periode);VALUES(Periode[Måned])) RETURN VAR NyeKunderIMaaned = [Total aktive kunder] - AktiveSidsteMaaned RETURN VAR KundeDageImaaned = ((AktiveSidsteMaaned * DageIMaaned) + (0,5*NyeKunderIMaaned*DageIMaaned)) RETURN CALCULATE( DageIMaaned * DIVIDE('Kunde Churn'[Churned Kunder];KundeDageImaaned) )
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |