FILTER Function


Dear All,


I would like to seek advice on the "Filter" formula:
Is that possible to use the filter formula to find the latest pay for each employee (Highlighted in yellow)?2021-10-02_15-29-24.jpg

3 Replies
best response confirmed by John_Lim (Visitor)

Hi @John_Lim 

I'd go with a helper column on my data to flag the latest as TRUE false, but you could do a mega formula




If the data is sorted by date - as in your example - then I would go for XLOOKUP().



As variant, assuming source data is in the table Payments and Person ID are numbers

  k, 10000000,
  id, UNIQUE(Payments[Person ID]),
  pos, XMATCH(id*k+DATE(9999,1,1), Payments[Person ID] *k+ Payments[End Date],-1),
 INDEX(Payments, pos, SEQUENCE(,COLUMNS(Payments)))