Forum Discussion

jrodriguez's avatar
jrodriguez
Regular Visitor
10 years ago
Solved

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:      Clos...
Need Help
  • asocorro's avatar
    asocorro
    10 years ago

    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's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
10 years ago

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's avatar
ImkeF
Icon for Community Champion rankCommunity Champion
10 years ago

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