Array Formula IF Condition Match, Return Latest Date

[matthewtjy]

Hi, I have 2 tables [SalesRecords] and [SalesPersonID].

[SalesRecords] has every sales transaction for every sales person;

[SalesPersonID] has the distinct record of every sales person ID.

How could I get the Latest Sales made by each sales person from the [SalesRecord] table as a created column in [SalesPersonID] table (expected result in red in [SalesPersonID] table below).

Normally I would use the array formula in Column D:2 in excel as such {=MAX(IF(C2=A:A,B:B))}

SalesRecords

	Column A	Column B
Row 1	SalesPersonID	SalesDate
Row 2	John	1 Jan 2020
Row 3	John	5 Feb 2020
Row 4	John	3 Mar 2020
Row 5	Tom	4 Feb 2020
Row 6	Tom	5 Feb 2020
Row 7	Harry	6 Jan 2020
Row 8	Harry	1 Mar 2020
Row 9	Harry	8 Mar 2020
Row 10	Harry	10 Mar 2020

SalesPersonID

	Column C	Column D
Row 1	SalesPersonID	LatestSalesDate
Row 2	John	3 Mar 2020
Row 3	Tom	5 Feb 2020
Row 4	Harry	10 Mar 2020

Many thanks!!

[Ashish_Mathur]

Hi,

Write this calculated column formula in the SalesPersonID table

=calculate(max(slesrecords[alesdate]),filter(salespersonid,salespersonid[salesperonid]=earlier(salesrecords[salespersonid])))

Hope this helps.

[ryan_mayu · Accepted solution]

@matthewtjy 

you can create a column

Column = maxx(FILTER('SalesRecords','SalesRecords'[SalesPersonID]='SaelsPersonID'[SalesPersonID]),'SalesRecords'[SalesDate])

[matthewtjy]

Thanks!