YoY Retention%

[Sudhir_M1992]

Please help me with DAX to calculate YoY Retention% one basis of email I'ds.

[Mrxiang]

RetentionRate = 
VAR TotalCustomers = SUM(CASE WHEN Name IS NOT NULL AND Email IS NOT NULL THEN 1 ELSE 0 END)
VAR UniqueCustomers = DISTINCTCOUNT(Name)
VAR TotalEmails = SUM(CASE WHEN Name IS NOT NULL AND Email IS NOT NULL THEN Email ELSE NULL END)
VAR UniqueEmails = DISTINCTCOUNT(Email)
RETURN 
IF(TotalCustomers > 0,
  IF(TotalCustomers > UniqueCustomers,
    RetentionRate = (TotalCustomers - UniqueCustomers) / UniqueCustomers * 100,
    0)
)

This measure calculates the total number of customers, unique customers, and total emails for each year. It then computes the retention rate by dividing the number of unique customers by the total number of customers, and adjusts the result if the number of customers is less than the number of unique customers. The result is then returned as a percentage.
Note that this measure assumes that each customer is unique, which may not be the case in your data. If some customers have the same email address, they will be counted as the same customer in the calculation of unique customers. If this is not the case, you may need to adjust the measure accordingly.

[Sudhir_M1992]

Hey @Mrxiang Thanks for your comment.

If possible, Could you please add this measure in PBIX file as I have already shared the sample data? 

[Mrxiang]

I didn't see the PBIX file you shared.Can you send me a demo?

[Sudhir_M1992]

Super!!!

Please help me with tihs

[v-jianboli-msft]

Hi @Sudhir_M1992 ,

What does your data look like?

Sorry for that the information you have provided is not making the problem clear to me. Can you please share more details to help us clarify your scenario?

Please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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

[Sudhir_M1992]

Thanks Jianboli. Here's the sample data now we have to calculate YoY Retention% of customers.

Name	Email	Year	Event ID
AB	AB@XYZ.COM	2020	A0
BC	BC@XYZ.COM	2021	B1
CD	CD@XYZ.COM	2023	C3
DE	DE@XYZ.COM	2022	D2
EF	EF@XYZ.COM	2022	E2
GH	GH@XYZ.COM	2020	G0
IJ	IJ@XYZ.COM	2021	I1
KL	KL@XYZ.COM	2023	K3
MN	MN@XYZ.COM	2022	M2
OP	OP@XYZ.COM	2022	O2
QR	QR@XYZ.COM	2020	Q0
ST	ST@XYZ.COM	2021	S1
UV	UV@XYZ.COM	2023	U3
WX	WX@XYZ.COM	2020	W0
YZ	YZ@XYZ.COM	2021	Y1
AB	AB@XYZ.COM	2021	A1
BC	BC@XYZ.COM	2022	B2
CD	CD@XYZ.COM	2020	C0
DE	DE@XYZ.COM	2023	D3
GH	GH@XYZ.COM	2022	G2
IJ	IJ@XYZ.COM	2023	I3
KL	KL@XYZ.COM	2021	K1
MN	MN@XYZ.COM	2020	M0