Forum Discussion
Array Formula IF Condition Match, Return Latest Date
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!!
you can create a column
Column = maxx(FILTER('SalesRecords','SalesRecords'[SalesPersonID]='SaelsPersonID'[SalesPersonID]),'SalesRecords'[SalesDate])
3 Replies
you can create a column
Column = maxx(FILTER('SalesRecords','SalesRecords'[SalesPersonID]='SaelsPersonID'[SalesPersonID]),'SalesRecords'[SalesDate])Thanks!
Hi,
Write this calculated column formula in the SalesPersonID table
=calculate(max(slesrecords[alesdate]),filter(salespersonid,salespersonid[salesperonid]=earlier(salesrecords[salespersonid])))
Hope this helps.
