Obtaining a column value based on two other columns' values

[dccosta]

So essentially I have a table 1 with budget data:

Flight date | Year | Budget Version | ammount

01/01 | 2023 | V1 | 1312

02/01 | 2023 | V1 | 1245

07/02 | 2023 | V2 | 2525

10/08 | 2023 | V3 | 2523

01/10 | 2023 | V4 | 724

01/01 | 2024 | V1 | 1341

And another table 2 with:

Year | Budget Version | Order

2023 | V1 | 1

2023 | V2 | 2

2023 | V3 | 3

2023 | V4 | 4

2024 | V1 | 5

The Order field is meant to tell the system which budget version is current/latest, which, for a specific year, is the maximum value of "order". So for example, for 2023, the current forecast version is V4, but for 2024, the current forecast version is V1.

I want to be able to have a dynamic measure (or calculated column) that can tell me, based on table 1's "Year", which is the current/latest forecast version, regardless of its original budget version. So for example, if I choose Table 1's registry 01/01 | 2023 | V1 | 1312, this new measure/column would say: "V4", because for the year 2023, it's the last of the versions currently available in table 2.

Hope I made myself clear...:)

[FreemanZ · Accepted solution]

hi @dccosta ,

try like:

Measure =

VAR _year = SELECTEDVALUE(table1[Year])

VAR _result =

MAXX(

    TOPN(

         1,

        FILTER(table2, table2[Year]=_year),

        table2[Order]

    ),

    table2[Budget Version]

)

RETURN _result