Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
matthewtjy
Helper I
Helper I

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 AColumn B
Row 1SalesPersonIDSalesDate
Row 2John1 Jan 2020
Row 3John5 Feb 2020
Row 4John3 Mar 2020
Row 5 Tom4 Feb 2020
Row 6Tom5 Feb 2020

Row 7

Harry

6 Jan 2020
Row 8Harry1 Mar 2020
Row 9Harry8 Mar 2020
Row 10Harry10 Mar 2020

 

SalesPersonID

 Column CColumn D
Row 1SalesPersonIDLatestSalesDate
Row 2John3 Mar 2020
Row 3Tom5 Feb 2020
Row 4Harry10 Mar 2020

 

Many thanks!!

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

@matthewtjy 

you can create a column

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ryan_mayu
Super User
Super User

@matthewtjy 

you can create a column

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.