How to make a new column with latest data

[MDO]

Hi,

I want to make a new column for showing the latest bin code for each serial no - I cannot figure it out how to do it.

I have tried Group By in Power Query, but doesn't work.

I want something like below. The first 4 column are raw data, and the last are the data I need. 

Registering Date	Quantity	Serial No_	Bin Code	LATEST BIN CODE
26-09-2023 00:00	1	SN000014	30601	RENT-0253
26-10-2023 00:00	-1	SN000014	TRANSIT	RENT-0253
27-09-2023 00:00	-1	SN000014	30601	RENT-0253
27-09-2023 00:00	1	SN000014	RENTAL	RENT-0253
26-10-2023 00:00	1	SN000014	RENT-0253	RENT-0253
13-03-2023 00:00	-1	SN166	TRANSIT	RENT-0188
03-02-2023 00:00	1	SN166	TRANSIT-REP	RENT-0188
06-02-2023 00:00	-1	SN166	TRANSIT-REP	RENT-0188
06-02-2023 00:00	1	SN166	RENT-0188	RENT-0188
27-02-2023 00:00	-1	SN166	RENT-0188	RENT-0188
13-03-2023 00:00	1	SN00329	70311	RENT-0226
04-07-2023 00:00	1	SN00329	30601	RENT-0226
13-06-2023 00:00	-1	SN00329	70311	RENT-0226
23-06-2023 00:00	-1	SN00329	30601	RENT-0226
23-06-2023 00:00	1	SN00329	11024	RENT-0226
13-06-2023 00:00	1	SN00329	RENT-0226	RENT-0226
04-07-2023 00:00	-1	SN00329	RENT-0226	RENT-0226
13-03-2023 00:00	1	SN00365	70311	11023
07-07-2023 00:00	-1	SN00365	70311	11023
07-07-2023 00:00	1	SN00365	11023	11023
28-11-2022 00:00	-1	SN753	TRANSIT	TRANSIT
15-02-2023 00:00	-1	SN753	TRANSIT	TRANSIT
22-12-2022 00:00	1	SN753	21413	TRANSIT
04-01-2023 00:00	-1	SN753	TRANSIT	TRANSIT

I hope someone can help me 🙂 - Thank you in advance.

[Kishore_KVN · Accepted solution]

Hello @MDO ,
Please find attached file for your reference. But the output is not mentioned correctly as per your example because for serial number SN166 latest date is 13th March so the Latest Bin Code will be TRANSIT not RENT-0188. So these things are taken care in the attached eample. 
DAX will be:

Latest Bin Calculated = 
Var One = CALCULATE(MAX('Table'[Registering Date]),ALLEXCEPT('Table','Table'[Serial No_]))
Var Result = CALCULATE(MAX('Table'[Bin Code]),FILTER('Table','Table'[Registering Date] = One))
Return
Result

 Output looks as below:

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!

[ThxAlot · Accepted solution]

Add an index column to differentiate codes registered on the same day.

[Ahmedx]

you wrote what you need
like this
09/26/2023 00:00 1 SN000014 30601 RENT-0253

[Ahmedx]

pls try this

[Kishore_KVN · Accepted solution]

Hello @MDO ,
Please find attached file for your reference. But the output is not mentioned correctly as per your example because for serial number SN166 latest date is 13th March so the Latest Bin Code will be TRANSIT not RENT-0188. So these things are taken care in the attached eample. 
DAX will be:

Latest Bin Calculated = 
Var One = CALCULATE(MAX('Table'[Registering Date]),ALLEXCEPT('Table','Table'[Serial No_]))
Var Result = CALCULATE(MAX('Table'[Bin Code]),FILTER('Table','Table'[Registering Date] = One))
Return
Result

 Output looks as below:

If this post helps, then please consider accepting it as the solution to help other members find it more quickly. Thank You!!