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...:)
