Forum Discussion

MDO's avatar
MDO
Frequent Visitor
2 years ago
Solved

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 somet...
Need Help
  • Kishore_KVN's avatar
    Kishore_KVN
    2 years ago

    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's avatar
    ThxAlot
    2 years ago

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

Kishore_KVN's avatar
Kishore_KVN
Icon for Solution Sage rankSolution Sage
2 years ago

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!!