Add latest date by item to data

Question

Hello!&nbsp;Another day, another Power BI issue...&nbsp;I have a dataset consisting of a unique list of customers, something like this:Contract IDCustomer NameSales PersonPost Code Area1234Customer ATim JonesBS1235Customer BTim JonesTA1236Customer CTim JonesEX1237Customer DSarah RobertsWA1238Customer ATim JonesBS1239Customer ATim JonesBS1240Customer DSarah RobertsWA1241Customer ESarah RobertsSY1242Customer BTim JonesTA1243Customer ESarah RobertsSY1244Customer DSarah RobertsWA1245Customer FSarah RobertsCW&nbsp;&nbsp;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 HISTORYContract IDProductPurchase Date1237Product A03/01/20231244Product C03/01/20231244Product A09/01/20231235Product B15/01/20231235Product A23/02/20231235Product B24/02/20231234Product A08/03/20231242Product B01/04/20231242Product C01/04/20231245Product C22/03/20231245Product A25/03/20231245Product A25/03/20231245Product A03/04/20231240Product B30/01/2023&nbsp;I need to either add a column to the customers list stating the last purchase date, e.g...&nbsp;Contract IDCustomer NameSales PersonPost Code AreaLast Purchase1234Customer ATim JonesBS08/03/20231235Customer BTim JonesTA24/02/20231236Customer CTim JonesEXNULL1237Customer DSarah RobertsWA03/01/20231238Customer ATim JonesBSNULL1239Customer ATim JonesBSNULL1240Customer DSarah RobertsWA30/01/20231241Customer ESarah RobertsSYNULL1242Customer BTim JonesTA01/04/20231243Customer ESarah RobertsSYNULL1244Customer DSarah RobertsWA09/01/20231245Customer FSarah RobertsCW03/04/2023&nbsp;Or to create a new measure that will do the same thing.&nbsp;Ultimately, I'm looking to map a sales persons customers and colour code them according to whether they've made a purchase or not.&nbsp;Any help, as always would be greatly appreciated.&nbsp;ThanksRDF

bhelou · Accepted Answer

Dear ,&nbsp;In power Query do the same as follow :&nbsp;1 - Group list 2 by max date&nbsp;2 - Merge Queries in list 1 with list 2&nbsp;3- Expand List 2 , it will show up the max date that match with the contract ID&nbsp;Attached Images and Attached PBIX file link&nbsp;https://www.dropbox.com/s/e13zbo13scm5myt/Max%20Date.pbix?dl=0&nbsp;Regards ,&nbsp;Kindly accept as a solution and a kudo is appreciated .&nbsp;List 2 OriginalGroup By Max DateList 1 OriginalMerge  list 1 and 2 ,Expand max dateReport

Forum Discussion

Add latest date by item to data

2 Replies

Related content

Latest Date Filter

Between date filter with fixed start date and latest end date

Datediff between latest date and todays date

Dynamic Date Slicer - Default to Latest Available Date with Data

Power Bi latest Date column

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

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