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.
Hey,
i have a table with company names in row 1, a year in row 2 and sales in row 3. The sales sum indicates the sales for that specific cutomer in that year.
Customer 1 | 2018 | 10.000,- $ |
Customer 2 | 2019 | 12.000,- $ |
Customer 1 | 2019 | 13.000,- $ |
Customer 3 | 2020 | 9.000,- $ |
I want to find out, which customer had sales this year (and how much) but didnt' have any sales the last 2 years (= new customer).
I'm currently stuck, at the point, that i have the same customer name multiple times in the same table. so all my filter, group by attemps etc. failed.
Thank you for any tip 🙂
Solved! Go to Solution.
Hi, @Anonymous
According to your description, you can follow my steps:
Flag =
var _year='Table'[year]
var _company='Table'[company name]
return
if(
(CALCULATE(
MAX([sales ]),
FILTER('Table',
'Table'[year]=_year-2
&&
'Table'[company name]=_company))>0
||
CALCULATE(
MAX([sales ]),
FILTER('Table',
'Table'[year]=_year-1
&&
'Table'[company name]=_company))>0)
&&
[sales ]>0,
0,
1
)
New customer =
FILTER('Table',
[year]=YEAR(TODAY())
&&
'Table'[Flag]=1)
And you can get what you want, like this:
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
According to your description, you can follow my steps:
Flag =
var _year='Table'[year]
var _company='Table'[company name]
return
if(
(CALCULATE(
MAX([sales ]),
FILTER('Table',
'Table'[year]=_year-2
&&
'Table'[company name]=_company))>0
||
CALCULATE(
MAX([sales ]),
FILTER('Table',
'Table'[year]=_year-1
&&
'Table'[company name]=_company))>0)
&&
[sales ]>0,
0,
1
)
New customer =
FILTER('Table',
[year]=YEAR(TODAY())
&&
'Table'[Flag]=1)
And you can get what you want, like this:
You can download my test pbix file here
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey @v-robertq-msft ,
your solution works great! Thank you!
Now i have to learn, what you exactly did 🙂
Take it step by step
- for each customer and year find the value.
- find the latest previous year with a value for the same customer
- if found nothing, or if the year is at least 2 years earlier than the current one, flag the entry as new customer.
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 |
---|---|
106 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
143 | |
104 | |
103 | |
82 | |
70 |