calculate a coloumn based on variable in other table

[Alirezam]

Hi experts,

I need your help.

In table 1 I have "date" and "HDD"

In table 2, I have "date", "Energy", "ratio"

I want to add a column to table 2, calculating "Energy"="HDD"x"ratio"

This shold be only for the "HDD' that its "date" in table 1 matches to that of Table 2. (e.g. for Jan 2018 I must get 10x1.5=15

Table1

date                HDD

Jan 2018          10

Mar 2018         20

Table 2

date              Energy       ratio

Feb 2018         ?               1

Jan 2018        ?               1.5

[Anonymous · Accepted solution]

@Alirezam 

Energy = LOOKUPVALUE(Table1[HDD],Table1[Date],Table2[Date])*Table2[Ratio]

Try above calculated column this will work in case there is only one record exist for each date of table 2 in table 1 date column

[Alirezam · Accepted solution]

I do appreciate your reply. It helped me a lot. Just a quick one, if in the Table 1 I have more than 1 value for each date and want to find it by condition (e.g. if the "date" >Nov. 2019) how should I modify the command you sent through? Many thankx.

[Anonymous · Accepted solution]

@Alirezam 

Energy = LOOKUPVALUE(Table1[HDD],Table1[Date],Table2[Date])*Table2[Ratio]

Try above calculated column this will work in case there is only one record exist for each date of table 2 in table 1 date column