create a column looking for a value in the same table

[snifer]

table1					result
id	reference	date			reference-date
1	100	05/08/2018			06/08/2018
100		06/08/2018			06/08/2018
200	1	30/06/2018			05/08/2018

I got  a table1, in the row "id" 1 I need to see the reference number "100" search between "id" if match copy the date of this "ID" 100  ( in this case 06/08/2018)

the result has to be like an extra column in the table with the related date if found

if no reference number (not match or empty), just copy the date of the id without reference or not matching

and so on for all row

I hope to be clear

[Greg_Deckler · Accepted solution]

Column = 
MAXX(FILTER(ALL(Table1),[id]=EARLIER([reference])),[date]) 

[snifer]

in the case,  "reference" is not matching "id ", it copy the id date as for line starting with id 100

d	reference	date			reference-date
1	100	05/08/2018			06/08/2018
100		06/08/2018			06/08/2018( this one)
200	1	30/06/2018			05/08/2018

[PattemManohar]

@snifer Please try this...

ReferenceDate = 
VAR _lkpRef = LOOKUPVALUE(LkpIDRef[Date],LkpIDRef[ID],LkpIDRef[Reference])
RETURN FORMAT(IF(ISBLANK(_lkpRef),LkpIDRef[Date],_lkpRef),"dd MMMM yyyy")