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

Question

&nbsp;I have a table showing yearly Sales and Profit values at specific years for two different companies:&nbsp;Another table specifies reference years for each company.&nbsp;&nbsp;&nbsp;&nbsp;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:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Thanks for any help on this!&nbsp;Karim&nbsp;&nbsp;

zubair_muhammad · Accepted Answer

Anonymous
&nbsp;
Try with this column
&nbsp;
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] )
    )
)

Forum Discussion

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

2 Replies

Related content

TMDL - calculated column

Calculated Column across row context

Direct Lake Model - Calculated Column/Tables

Calculated Column removing filter context

Calculated column, measures and evaluation context