Max date per unique id

[cottrera]

Hi 

I have a facts table with two columns  Unit ref, Completed Data. The unit ref can appear on more than one line , but with a different  completed date.  

I need a measure column that displays the Max Dates for each of the Unit refs

Unit ref	Completed Date	DAX Max Date
437347	11/08/2019	11/08/2019
87678	30/05/2010	16/04/2015
87678	23/08/2011	16/04/2015
87678	16/04/2015	16/04/2015
18	04/04/2021	02/02/2022
18	02/02/2022	02/02/2022
7654	30/05/2010	01/03/2022
7654	05/05/2011	01/03/2022
7654	09/04/2012	01/03/2022
7654	15/03/2013	01/03/2022
7654	18/02/2014	01/03/2022
678	01/03/2022	01/03/2022
68696	23/05/2018	02/04/2020
68696	28/04/2019	02/04/2020
68696	02/04/2020	02/04/2020

I would also be interested in how this could be done in power query.

thank you

RIchard

[cottrera]

Thank you it works fine 😀

[PC2790 · Accepted solution]

Hey @cottrera ,

It can be done using "Group By" in Power Query.

See the screenshot of the parameters arrangement in Group By

And this is the advanced editor code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc/LDcQwCATQXnyOxB/jWqL030YCa8lCe30ChrnvoTJF57gGEQQw0hrPdY+YPuNTQUBLxsYsgDVNjckBNdmKKU0hhekIA6dwyXTT/5ytBnZCtq26R22bDFBSpWkAZhJp6X4R5IR7+PJdp5Kic+w6q/Ov0ffp8wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unit ref" = _t, #"Completed Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unit ref", Int64.Type}, {"Completed Date", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Unit ref"}, {{"Max", each List.Max([Completed Date]), type nullable text}, {"All rows", each _, type table [Unit ref=nullable number, Completed Date=nullable text]}}),
    #"Expanded All rows" = Table.ExpandTableColumn(#"Grouped Rows", "All rows", {"Completed Date"}, {"Completed Date"})
in
    #"Expanded All rows"

The outcome will be: