Returning latest among multiple dates by row?

[tfoss78]

Newer user, apologies in advance if I'm missing something basic here.  The table I'm using includes several dates per row corresponding to the receipt of various documents, I am trying to build a custom column that will return the latest of these dates per row.  It's my sense that MAX on the DAX side is entirely out, I've attempted to use the following script in Power Query to no avail ("a cyclic reference was enountered..."):

Table.AddColumn(#"Changed Type", "Custom", each Table.AddColumn(table, "Max", each List.Max({[receipt of document 1], [receipt of document 2], [receipt of document 3], [receipt of document 4], [receipt of document 5], [receipt of document 6], [receipt of document 7]})))

I would be eternally grateful to anyone who can set me on the right path!  Thanks very much for reading.

[v-yanjiang-msft · Accepted solution]

Hi @tfoss78 ,

There two Table.AddColumn in you code. 

I get the correct result by add a custom column.

List.Max({[Column1],[Column2],[Column3]})

Here's the whole code below, you can copy-paste in a blank query to see the step.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtI31zc0UtKBsxFMM6VYHbgaY4S4EZJyC2Q1SOKmCKalUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type date}, {"Column3", type date}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Max", each List.Max({[Column1],[Column2],[Column3]}))
in
    #"Added Custom1"

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75]

You can use MAX, you just need to put the columns into a table variable, like

Max of columns = MAXX( { 'Table'[Col1], 'Table'[Col2], 'Table'[Col3] }, [Value])

[tfoss78]

Hi John, thanks very much for this bit of insight.  However, I'm encountering a problem with the value part of the expression - I don't need any calculation per se, just that it return the latest among seven dates specified in the first part of the expression.  Is this possible using MAXX?  Sorry if I'm being dim here.  

[johnt75]

The table constructor { } creates a table with a single column called [Value]. By adding all the dates you want to compare into a table like that you can run MAXX over it.