Offset + Match (Excel) equivalent in DAX

[jeyare]

In a learning mode,  can't find a solution for this example (simplified). Thank you for your tip.

to column "Index W":

- I need to MATCH the nearest greater value from "Weight B" for each evaluated row value of "Age A"

- then I need to write the value of "Weight A" matched

In Excel it is easy done by:

OFFSET(A1, MATCH(Age A, Age B , 1 ), 1)

Sample:

Age A	Weight A	Age B	Index W
1.0	10	1.1
1.2	20	3.2
3.6	30	3.7

Expected result:

Age A	Weight A	Age B	Index W
1.0	10	1.1	20
1.2	20	3.2	30
3.6	30	3.7	blank

[jeyare]

Thx gents for the advice.

[CNENFRNL · Accepted solution]

Index W = 
MAXX(
    TOPN(
        1,
        FILTER( DATA, DATA[Age B] > EARLIER( DATA[Age B] ) ),
        DATA[Age A], ASC
    ),
    DATA[Weight A]
)

[Jihwan_Kim]

Hi,

The below is for creating a calculated column.

Index W CC =
VAR _ageB = Data[Age B]
VAR _condition =
MINX ( FILTER ( Data, Data[Age A] > _ageB ), Data[Age A] )
RETURN
CALCULATE ( SUM ( Data[Weight A] ), FILTER ( Data, Data[Age A] = _condition ) )

Link to the sample pbix file