Picking MAX date from 3 date columns

[jrodriguez]

Hey Guys,

Can you help me with the following? I need syntax that gives me the most recent date from 3 different date columns.....something like this: 

Opend Date:      Pending Date:      Close Date:         Max Date from the 3:

    1/2/2015               1/4/2015            1/5/2015                          X 

In tableau it's just a max function. Let me know if you can help me out. Thanks guys

Joe

[asocorro · Accepted solution]

Well, if it's just 3, try this:

SWITCH(True(),

D1 > D2, IF(D1 > D3, D1, D3),

True(), IF(D2 > D3, D2, D3)
)

And in case your data is from SQL Server, you can calculate it in the query.  For example:

[ImkeF]

If you would do this in the query editor instead, your M-code would actually resemble the Tableau syntax:

List.Max({[#"Opend Date:"],[#"Pending Date:"],[#"Close Date:"]})

There are some performance aspects of M vs DAX as well.

https://blog.oraylis.de/2016/02/relative-week-colu​mn-with-dax-power-query/

"...Actually the Power Query version is slightly faster, because the Vertipaq-Engine can do a better compression with a column which is set during data load. The DAX generated columns will be created during processing the model. This has two negative impacts. First the compression won’t be as good and second every time the cube is processed, for example when a new measure is created, Power BI Desktop will recreate the two columns, which will take more time..."

[ImkeF]

Anyone any idea why the editor keeps eating the closing brackets?