Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!
Solved! Go to Solution.
you can create a column
Column = maxx(FILTER('SalesRecords','SalesRecords'[SalesPersonID]='SaelsPersonID'[SalesPersonID]),'SalesRecords'[SalesDate])
Proud to be a Super User!
Hi,
Write this calculated column formula in the SalesPersonID table
=calculate(max(slesrecords[alesdate]),filter(salespersonid,salespersonid[salesperonid]=earlier(salesrecords[salespersonid])))
Hope this helps.
you can create a column
Column = maxx(FILTER('SalesRecords','SalesRecords'[SalesPersonID]='SaelsPersonID'[SalesPersonID]),'SalesRecords'[SalesDate])
Proud to be a Super User!
Thanks!
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |