Forum Discussion
create a column looking for a value in the same table
| 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
Column = MAXX(FILTER(ALL(Table1),[id]=EARLIER([reference])),[date])
3 Replies
Column = MAXX(FILTER(ALL(Table1),[id]=EARLIER([reference])),[date])
Anonymous Please try this...
ReferenceDate = VAR _lkpRef = LOOKUPVALUE(LkpIDRef[Date],LkpIDRef[ID],LkpIDRef[Reference]) RETURN FORMAT(IF(ISBLANK(_lkpRef),LkpIDRef[Date],_lkpRef),"dd MMMM yyyy")
