Custom Column please help!!!!

Hi Mattym80 

According to your needs , I created two calculated columns .

Column = CALCULATE(MAX('Table'[date]),FILTER(ALLEXCEPT('Table','Table'[Postion]),'Table'[date]<EARLIER('Table'[date])))

Column 2 = 
var _date='Table'[Column]
RETURN CALCULATE(MAX('Table'[ID]),FILTER(ALLEXCEPT('Table','Table'[Postion]),'Table'[date]= _date))

Add these two columns to your table , you will get a result like below .

In your result ,when "Postion" = 2 , "ID" = 3 , the return value should be 1 .

I have attached my pbix file , you can refer to it .

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Mattym80 ,

In PowerQuery, 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFLDsMgDESvErGOgjGfJBfoJSIWrdQLROqit29obSuI4Ep4gfzGeJhtM+CG2/Oxv+77e0BANKNxpyonj5cYo6hjnjCvY4EmBR2LhMUzhg2W6NGkY+xyPgqcBZws4kStjmYmzaKPXghbdUx+CHgFK52OZGVJlY+/NPflyuWYW0bX5lqNfIr/aVDW8V0JsKaKLrQgR+ziH5BDdkkHPXOSnxXvOX8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, Postion = _t, ID = _t, Index = _t, #"Column 1 Return Date" = _t, #"Column 2 retun ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"Postion", Int64.Type}, {"ID", Int64.Type}, {"Index", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"date", "Postion", "ID", "Index"}),
    #"Grouped Rows" = Table.Group(#"Removed Other Columns", {"Postion"}, {{"Rows", each _, type table [date=nullable date, Postion=nullable number, ID=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each let
AllList = Table.ToColumns([Rows]),
ReturnDate = {null} & Table.RemoveLastN([Rows],1)[date],
ReturnID = {null} & List.RemoveLastN(_[Rows][ID],1),
CombineLists = AllList & {ReturnDate} & {ReturnID} ,
Columns = Table.ColumnNames([Rows])
in
Table.FromColumns(CombineLists, Columns & {"Return Date", " Return ID"})),
    Expanded = Table.Combine(#"Added Custom"[Custom]),
    #"Changed Type1" = Table.TransformColumnTypes(Expanded,{{"Return Date", type date}, {" Return ID", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type1",{{"Index", Order.Ascending}})
in
    #"Sorted Rows"