Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a set of data with sales from customers. What I want to know is how long do new customers stay at my organisation? So if they buy for the first time in 2013. Do they buy also in 2014? And in 2015? So my solution should look like:
Year | New customers | Active after 1 year | Active after 2 years | Active after 3 years |
2013 | 100 | 90 | 60 | 30 |
2014 | 200 | 190 | 150 | 90 |
2015 | 150 | 135 | 125 | 100 |
2016 | 350 | 335 | 296 | 250 |
2017 | 250 | 224 | 200 | 175 |
2018 | 150 | 145 | 135 | 120 |
My data is a fact Sales with dim Customers and dim Datetable. In fact sales I have transactiondate, amount, sales and keys to dim tables.
Thank you for your help!
Regards,
Jarno Visser
Source data would really, really help here. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
But, that being said, you sould be able to use a variation of my Not In Common Quick Measure to find customers in a particular year that are not present in the years before.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Not-In-Common/m-p/388273
That would solve New Customers. After that, it should be a relatively simple matter to compute the MAX order date for each customer and determine if they are active after a length of time.
Very interesting case and would love to spend some cycles on it but too much work to hand enter a bunch of data in the wrong format such that it doesn't translate directly to a solution for you.
Hi Greg,
Here is a file with data from my fact table: https://we.tl/t-jbGwLywdWB
The dim table customers you could create with values(customerid).
Hope you can help me!
Regards,
Jarno
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |