Add latest date by item to data

[RDF25087]

Hello!

Another day, another Power BI issue...

I have a dataset consisting of a unique list of customers, something like this:

Contract ID	Customer Name	Sales Person	Post Code Area
1234	Customer A	Tim Jones	BS
1235	Customer B	Tim Jones	TA
1236	Customer C	Tim Jones	EX
1237	Customer D	Sarah Roberts	WA
1238	Customer A	Tim Jones	BS
1239	Customer A	Tim Jones	BS
1240	Customer D	Sarah Roberts	WA
1241	Customer E	Sarah Roberts	SY
1242	Customer B	Tim Jones	TA
1243	Customer E	Sarah Roberts	SY
1244	Customer D	Sarah Roberts	WA
1245	Customer F	Sarah Roberts	CW

I also have a list of product sales by some (but not all of the above customers). In some instances a customer may have made multiple purchases. So, the sales history table would look something like this:

PURCHASE HISTORY
Contract ID	Product	Purchase Date
1237	Product A	03/01/2023
1244	Product C	03/01/2023
1244	Product A	09/01/2023
1235	Product B	15/01/2023
1235	Product A	23/02/2023
1235	Product B	24/02/2023
1234	Product A	08/03/2023
1242	Product B	01/04/2023
1242	Product C	01/04/2023
1245	Product C	22/03/2023
1245	Product A	25/03/2023
1245	Product A	25/03/2023
1245	Product A	03/04/2023
1240	Product B	30/01/2023

I need to either add a column to the customers list stating the last purchase date, e.g...

Contract ID	Customer Name	Sales Person	Post Code Area	Last Purchase
1234	Customer A	Tim Jones	BS	08/03/2023
1235	Customer B	Tim Jones	TA	24/02/2023
1236	Customer C	Tim Jones	EX	NULL
1237	Customer D	Sarah Roberts	WA	03/01/2023
1238	Customer A	Tim Jones	BS	NULL
1239	Customer A	Tim Jones	BS	NULL
1240	Customer D	Sarah Roberts	WA	30/01/2023
1241	Customer E	Sarah Roberts	SY	NULL
1242	Customer B	Tim Jones	TA	01/04/2023
1243	Customer E	Sarah Roberts	SY	NULL
1244	Customer D	Sarah Roberts	WA	09/01/2023
1245	Customer F	Sarah Roberts	CW	03/04/2023

Or to create a new measure that will do the same thing.

Ultimately, I'm looking to map a sales persons customers and colour code them according to whether they've made a purchase or not.

Any help, as always would be greatly appreciated.

Thanks

RDF

[RDF25087]

@bhelou  - thank you for your solution, and especially for the step-by-step images - they were especially useful for a beginner like myself.

I got a little confused about how to Expand the column. After the merge I had a column in my data displaying "table". After a little more googling, I realised I simply had to click on the double-arrow icon in the column header and select the column I wanted to bring in/expand. Hope that helps any other newbie like myself that need everything spelt out for them!

Regards

RDF

[bhelou · Accepted solution]

Dear , 

In power Query do the same as follow : 

1 - Group list 2 by max date 
2 - Merge Queries in list 1 with list 2 
3- Expand List 2 , it will show up the max date that match with the contract ID 

Attached Images and Attached PBIX file link 

https://www.dropbox.com/s/e13zbo13scm5myt/Max%20Date.pbix?dl=0 

Regards , 

Kindly accept as a solution and a kudo is appreciated . 

List 2 OriginalGroup By Max DateList 1 OriginalMerge list 1 and 2 ,Expand max dateReport