retrieve Max value based on other columns

[SadStatue]

Hi Everybody,

I have a table "MainTable" as follow:

ID	complete	Name	from	to
88	27/06/2019	Elly	5/09/2019	12/10/2019
88	27/06/2019	Ann	4/01/2019	10/10/2019
88	27/06/2019	Lary	1/07/2017	10/10/2019
22	27/06/2019	Joe	5/09/2019	12/10/2019
22	27/06/2019	Henry	4/01/2019	10/06/2019
22	27/06/2019	Adam	1/07/2017	10/10/2019
53	1/07/2019	Mary	5/01/2019	12/10/2019
53	1/07/2019	Ferry	4/01/2019	10/10/2019
53	1/07/2019	Lucy	1/07/2017	10/10/2019

Each ID has a single Complete Date, but has different name with different From & To dates.

What I want to extract from this table is: Getting the maximum From date for each ID where the Complete date is between From & To date. So the result table should be something like that:

ID	Complete	Name	from	to
88	27/06/2019	Ann	4/01/2019	10/10/2019
22	27/06/2019	Adam	1/07/2017	10/10/2019
53	1/07/2019	Mary	5/01/2019	12/10/2019

I would be glad if anyone can help me deal with the situation whether using DAX or Power querry.

Cheers,

[Zubair_Muhammad · Accepted solution]

Hi @SadStatue 

Try this M code.

Please see attached file as well

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsrBQ0lEKySgtKk5JrNRR8CrNS1UwMtdRMDIwtATKuObkVKIoCE4tKEnNTUotUjCFKwpOLCktAsv6J5fkg+QMjaCSsToErXDMywOSbkWZENnEvNLEokoFE7g8QifcdAPiTfcBGobiRK/SnEoFQ7C8OWHTjYzwmu6Vn0pp6BCwwSM1D+wB3OHjm5+H0EmK4x1TEnMpCBpTYxTL4VrBbsIIdaiziQ4WfKa7pRYRCBMKnO5Tmkx2gokFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, complete = _t, Name = _t, from = _t, to = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"complete", type date}, {"Name", type text}, {"from", type date}, {"to", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "complete"}, {{"AllRows", each _, type table [ID=number, complete=date, Name=text, from=date, to=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.Max(
    Table.SelectRows([AllRows],(x)=>[complete]>=x[from] and [complete]<=x[to]),
    "from")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom", "Custom", {"Name", "from", "to"}, {"Name", "from", "to"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"AllRows"})
in
    #"Removed Columns"

[SadStatue]

Hi Zubair,

Thank you so much for your response. It is working as I wanted.