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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sudhir_M1992
Frequent Visitor

YoY Retention%

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

6 REPLIES 6
Mrxiang
Helper II
Helper II

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.

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? 

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

Sudhir_M1992
Frequent Visitor

Super!!!

Please help me with tihs

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.

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

NameEmailYearEvent ID
ABAB@XYZ.COM2020A0
BCBC@XYZ.COM2021B1
CDCD@XYZ.COM2023C3
DEDE@XYZ.COM2022D2
EFEF@XYZ.COM2022E2
GHGH@XYZ.COM2020G0
IJIJ@XYZ.COM2021I1
KLKL@XYZ.COM2023K3
MNMN@XYZ.COM2022M2
OPOP@XYZ.COM2022O2
QRQR@XYZ.COM2020Q0
STST@XYZ.COM2021S1
UVUV@XYZ.COM2023U3
WXWX@XYZ.COM2020W0
YZYZ@XYZ.COM2021Y1
ABAB@XYZ.COM2021A1
BCBC@XYZ.COM2022B2
CDCD@XYZ.COM2020C0
DEDE@XYZ.COM2023D3
GHGH@XYZ.COM2022G2
IJIJ@XYZ.COM2023I3
KLKL@XYZ.COM2021K1
MNMN@XYZ.COM2020M0

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.