Filter companies in one table (with multiple entries)

[Anonymous]

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 🙂

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, you can follow my steps:

1. Create a calculated column:

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
)

2. Create a calculated table:

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.

[Anonymous]

Hey @v-robertq-msft ,

your solution works great! Thank you!

Now i have to learn, what you exactly did 🙂

[lbendlin]

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.