Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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?
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.
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.
Proud to be a Super User!
User | Count |
---|---|
84 | |
84 | |
68 | |
62 | |
56 |
User | Count |
---|---|
137 | |
110 | |
92 | |
84 | |
69 |