Forum Discussion
How to make a new column with latest data
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.
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 ResultOutput 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!!
Add an index column to differentiate codes registered on the same day.
4 Replies
- Kishore_KVN
Solution Sage
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 ResultOutput 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
Super User
Add an index column to differentiate codes registered on the same day.
- Ahmedx
pls try this
- Ahmedx
you wrote what you need
like this
09/26/2023 00:00 1 SN000014 30601 RENT-0253
