cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jrodriguez Frequent Visitor
Frequent Visitor

Picking MAX date from 3 date columns

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                          

 

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

 

Joe

1 ACCEPTED SOLUTION

Accepted Solutions
asocorro Member
Member

Re: Picking MAX date from 3 date columns

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:

 

img3.png

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
3 REPLIES 3
asocorro Member
Member

Re: Picking MAX date from 3 date columns

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:

 

img3.png

Connect with me in LinkedIn: https://pr.linkedin.com/in/adolfosocorro
Follow me on Twitter: https://twitter.com/AdolfoSocorro
Super User
Super User

Re: Picking MAX date from 3 date columns

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..."

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Picking MAX date from 3 date columns

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries