Lookup for the latest value

[Leon1213]

Hi all,

I have 2 tables.

Table1:

ID     Type

1         A

2         B

3         C

Table2:

Type    value    timestamp

A             2         01-09-2018 15:51

A             4         01-09-2018  12:34

A             5         01-09-2018  07:15 

B             6         01-09-2018  18:42

B             7         01-09-2018  19:23

C            12        01-09-2018  02:14

I want to create a column named "latest_value" which will give the latest value for each Type.

The updated Table1 should be :

ID     Type    latest_value

1         A              2

2         B              7

3         C              12

[v-piga-msft]

Hi @Leon1213,

The solution from Zubair_Muhammad should solve your problem.

If you have solved, please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

Best  Regards,

Cherry

[Zubair_Muhammad]

@Leon1213

Assuming 2 tables are related by TYPE column

You could use this calc column in Table 1

Latest_Value (Calc Column) =
VAR my_date =
    CALCULATE ( MAX ( Table2[timestamp] ) )
RETURN
    CALCULATE ( SUM ( Table2[value] ), Table2[timestamp] = my_date )

[Zubair_Muhammad · Accepted solution]

@Leon1213

Infact this formula is simpler and should work as well

lastest_value =
CALCULATE ( SUM ( Table2[value] ), LASTNONBLANK ( Table2[timestamp], 1 ) )

[Leon1213]

Hi,

I used the following formula and it worked.

Please check if it is correct.

latest_value = CALCULATE(LOOKUPVALUE('Table2'[value],'Table2'[timestamp],max('Table2'[timestamp])),filter('Table2','Table2'[Type]='Table1'[Type]))

[SudarshanK]

This worked perfectly!! Here is fomatted version of the formula so it is easier to read:

latest_value = 
CALCULATE
	(
		LOOKUPVALUE
		(
			'Table2'[value]
			,'Table2'[timestamp]
			,MAX('Table2'[timestamp])
		)
		,FILTER
		(
			'Table2'
			,'Table2'[lookup_key]='Table1'[lookup_key]
		)
	)