Forum Discussion

Leon1213's avatar
Leon1213
Frequent Visitor
7 years ago
Solved

Lookup for the latest value

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...
Show and Tell
Tips and Tricks
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    Leon1213

     

    Infact this formula is simpler and should work as well

     

     

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

     

Leon1213's avatar
Leon1213
Frequent Visitor
7 years ago

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's avatar
SudarshanK
New Member
3 years ago

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]
		)
	)