New calculated column using row context to select value from same table

[Anonymous]

I have a table showing yearly Sales and Profit values at specific years for two different companies:

Another table specifies reference years for each company.

I would like a DAX expression to calculate a new column "Value_at_RefYear" showing for each company the Sales and Profit at reference year as specified in the RefYear table:

Thanks for any help on this!

Karim

[Zubair_Muhammad · Accepted solution]

@Anonymous

Try with this column

Value at Ref Year Column =
CALCULATE (
    SUM ( Sales_and_Profit[Value] ),
    FILTER (
        ALLEXCEPT (
            Sales_and_Profit,
            Sales_and_Profit[Company],
            Sales_and_Profit[Attribute]
        ),
        Sales_and_Profit[Year] = RELATED ( RefYear[Year] )
    )
)

[Anonymous]

Hi Zubair,

Thanks, it worked perfectly and also helped me understand a bit more about the combined use of the CALCULATE and FILTER functions.

Cheers

Karim