Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
99 | |
86 | |
78 | |
75 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |