powerbidaxdata-modelingcalculated-columnstabular

DAX - Inserting value into table from other table based on condition

I have two tables:

Table A (A simple 3 line table):

Year Objective
2021 2
2022 3
2023 3.5

Table B:

Date Value
10/09/2021 23
30/10/2021 24.4
22/05/2022 34
10/09/2022 23
11/03/2023 23.6
23/09/2023 27
ect... ect...

There is no relationship between these two tables.

What I need is a calculated column in Table B that will show the tableA.Objective based on if tableA.Year is equal to the year of TableB.Date. Like so:

Date Value My calculated column
10/09/2021 23 2
30/10/2021 24.4 2
22/05/2022 34 3
10/09/2022 23 3
11/03/2023 23.6 3.5
23/09/2023 27 3.5
etc... etc... etc...

However, I don't know how to create such a calculated column. Any help is greatly appreciated.

Thanks in advance!

Solution

  • As Ashok suggested,

    My calculated column = LOOKUPVALUE(TableA[Objective], TableA[Year], YEAR(TableB[Date]))