Filter companies by year but keep info about other years

[gorsheep]

I have a table (see Fig. 1) with three columns: company name, year, and sales. Years range from 2019 to 2021. I want to filter only companies that have data for 2021 and show their historic sales (see Fig. 2). I tried using "Table" and "Matrix" with a filter that uses year but it removes all data for previous years. What should I do?

Fig. 1:

Fig. 2:

  

[tamerj1 · Accepted solution]

Hi @gorsheep 

please try

Measure1 =
VAR MaxYearWithSales =
    CALCULATE ( MAX ( TableName[Year] ), REMOVEFILTERS () )
VAR CompaniesSoldMaxYear =
    CALCULATETABLE (
        VALUES ( TableName[Company] ),
        TableName[Year] = MaxYearWithSales,
        REMOVEFILTERS ()
    )
RETURN
    CALCULATE (
        SUM ( TableName[Sales] ),
        INTERSECT ( VALUES ( TableName[Company] ), CompaniesSoldMaxYear )
    )

[gorsheep]

You are truly a magician, thank you so much! Totally worked!