Use DAX to transpose column values based on relative date

[TimQ]

I need to transpose or unpivot text values into separate columns based on relative date. My data looks like this:

Unique ID	Date	Comment
1	7/1/2019	aaaaa
1	8/1/2019	bbbbb
1	9/1/2019	ccccc
2	7/1/2019	ddddd
2	8/1/2019	eeeee
2	9/1/2019	fffff
3	7/1/2019	ggggg
3	8/1/2019	hhhhh
3	9/1/2019	iiiii

I need to create separate columns that return the most recent, 2nd most recent, third most recent values in the Comment column based on the Date field per Unique ID. My end result would look something like this:

Unique ID	Most Recent Comment	2nd Most Recent Comment	Third Most Recent Comment
1	ccccc	bbbbb	aaaaa
2	fffff	eeeee	ddddd
3	iiiii	hhhhh	ggggg

I realize that the easiest way to address this is with the Query Editor, but unique circumstances require me to use DAX and calculated columns only. 

How would I go about creating these calculated columns?

[v-diye-msft]

Hi @TimQ 

Try below M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VczLCYAwEIThXuYciBsPmlrCHnxr/w3oRNhs/tPAB1MKBAFTlJgGyd9cGDT8MDdYmUFusLEKqb/amYG7OpiBuzpZhbG/upiBu7qZgbt6GFRf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, Date = _t, Comment = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Unique ID", Int64.Type}, {"Date", type date}, {"Comment", type text}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US")[Date]), "Date", "Comment")
in
    #"Pivoted Column"

Pbix attached.

[TimQ]

Thanks. This solution seems like it would work but due to a weird network circumstance I am unable to transform my data table in the query editor, so my solution would have to be executed as a series of DAX calculated columns in a visual.

[v-diye-msft · Accepted solution]

Hi @TimQ 

Did you try Matrix?