Calculate the max date in a row of dates

[prj102]

I just started working with Power BI desktop and noticed a few things that are giving me issues.  One of which is using the MAX() function with a row of dates.  In Excel, I was able to pass a range that contained multiple dates into the max function and it would return the latest date.  I am looking to do something similar. 

Example below

Name	Date1	Date2	Date3	MaxDate
Item1	11/13/2015	2/6/2016	2/5/2016	2/6/2016
Item2	1/13/2016	2/2/2016	2/7/2016	2/7/2016
Item3	2/8/2016	2/9/2016	11/26/2015	2/9/2016

[prj102]

Maybe this is a larger issue with what I am trying to do and maybe a little more info might help. 

I have 4 data sources that contains system names.  Each source contains health information for the systems and I need a way to check the health date info from all of the sources to see if the systems are healthy. 

I created a table using the following command.  UniqueData = DISTINCT(UNION(Source1[Name0], Source2[Name]))

This gave me a table called unique that I would use create a relationship with the other sources.  This Unique date table should have all the systems that the other sources has.  Problem I am having is that when I pull the data into a report, some of the systems are not included when filtering.  I expected to see a "Blank" or "#N/A" category when filtering but I do not see it.

[arify]

Can you try doing Table.AddColumn(table, "Max", each List.Max({[Column1], [Column2], [Column3]}) ?

[ryanperrymba]

Hi @arify 

You've suggested a max value can be added in this manner:

Table.AddColumn(table, "Max", each List.Max({[Column1], [Column2], [Column3]}) ?

Taking it one step further, how would one replace the explicit list of columns, "{[Column1], [Column2], [Column3]}", with a function that gets the columns by type? Example, I want to filter records based on ANY of the date columns having occured in the past 90 days. meaning I'd need to find the maximum of all the date fields, but not have to update the list if I later add another date column or remove one.

DateColumns = Table.ColumnsOfType(PreSelectColumns, {type datetime, type nullable datetime, type datetimezone, type nullable datetimezone}),
DatesTable = Table.SelectColumns(PreSelectColumns,DateColumns),
//AddMaxDateColumn = Table.AddColumn(PreSelectColumns, "MaxDate", each List.Max({[new_signedcontractdate], [newinspectioncompletedate]})),
//AddMaxDateColumn = Table.AddColumn(PreSelectColumns, "MaxDate", each List.Max(DateColumns)),
AddMaxDateColumn = Table.AddColumn(PreSelectColumns, "MaxDate", each List.Max(Table.ToList(DatesTable))),
FilterMaxDatetoPast90Days = Table.SelectRows(AddMaxDateColumn, each Date.IsInPreviousNDays([MaxDate], 90)),

You'll see I tried it first with explicit column names. I then tried it with a Columns of Type, but this doesn't work. The DateColumns line returns a list of column names as strings (IE, in quotes instead of square brackets).  The third, uncommented attempt to make the conversion doesn't work either. 

How would one filter for the maximum (or min) of multiple column values on each record?

[prj102]

I don't think I am following this solution.  Table doesn't seem to be an option.  Are you saying to create a new column and enter that line in the text box?

[arify]

Sorry, forgot to mention, my solution was in M. You can use that function that way in the Advanced Query Editor, using M language, not DAX

[SqlJason]

I guess you will have to use an IF statement to find the max across different columns, something like shown below

=IF('Table'[A]>='Table'[B] && 'Table'[A]>= 'Table'[C] && 'Table'[A]>= 'Table'[D],'Table'[A],IF('Table'[B]>='Table'[C]&&'Table'[B]>='Table'[D],'Table'[B],IF('Table'[C]>='Table'[D],'Table'[C],'Table'[D])))

The MAX function in DAX only returns the max in one column

[itchyeyeballs]

You could unpivot (powerquery) your data so that all the dates are in a single column with a seperate column to identify the date type, MAX() will then work