Custom Column please help!!!!

[Mattym80]

Hi

Im hoping someone can help please!!!

I am trying to create 2 custom columns please see example sheet below.

I am wanting to return the max date when the "Postion" is the same value and < index number (doesnt have to have an index column thats just the way I was trying to solve it just has to be a prior date)

I am also wanting to return the ID also were there is a prioir date for that postion and date match

date	Postion	ID	Index	Column 1 Return Date	Column 2 retun ID
01/02/2022	1	1	1
01/02/2022	2	1	2
01/02/2022	3	1	3
01/02/2022	4	2	4
01/02/2022	5	2	5
02/02/2022	6	1	6
02/02/2022	1	1	7	01/02/2022	1
02/02/2022	7	1	8
02/02/2022	8	1	9
02/02/2022	2	3	10	01/02/2022	2
02/02/2022	9	3	11
03/02/2022	1	3	12	02/02/2022	1
03/02/2022	2	1	13	02/02/2022	3
03/02/2022	20	1	14
04/02/2022	24	2	15
04/02/2022	25	2	16
04/02/2022	3	2	17	01/02/2022	1

[Anonymous · Accepted solution]

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.

[Mattym80]

Hi @latimeria @Anonymous  Great stuff both examples were spot on,  great having 2 options now one for dax and one for M. Thank you both for your support.

[latimeria · Accepted solution]

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"

[Anonymous · Accepted solution]

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.