Lost, retained and new customers year on year

[bml123]

Hi,

I have a requirement to get the number of clients lost, retained and new customers when comparing an year's figures against previous year's figures. 

Lost is when they appear in previous year and not appearing in current year

Retained - when they appear in previous and current year

New - when they appear in current year and not in previous year

How do I achieve that?

[v-jingzhang]

Hi @bml123 

What data or columns do you already have in the source table? Can you please provide some dummy data for us to use? 

If you want to use a measure to calculate the results, you can first use two variable tables in them to get the customer ids in each year separately. Then use EXCEPT and INTERSECT to compare two years' values. For those exist in two variable tables, they are retained customers. For those only exist in one variable table, they are new or lost customers. Count the numbers of above customers and you will get the result you want. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[vanessafvg]

the brilliant folks at sqlbi have created these patterns for this purpose.

https://www.daxpatterns.com/new-and-returning-customers/

you can adjust the code to your liking

the briliant sqlbi crew have created patterns specifically to assist with this.