Get MAX value of dates across multiple columns

[PhotoRonin]

In my data set I have user accounts listed with the date they last used a particular product (up to 10 products):

User  |  Product 1 Use Date  |  Product 2 Use Date

user1 |  2019-01-01              |  2019-01-20

user2 |  2018-01-29              |  2019-01-05

What I'd like to do is create a measured column that gives the MAX date among the dates listed for that user.  So in the example above, it would calculate 2019-01-20 for user1 and 2019-01-29 for user2.  What would my measured column need to be? the MAX function seems to only compare two items...

The data sets come from CSV files, and I'm trying to get MAX date (last use date of any product) for each user.

[v-yulgu-msft · Accepted solution]

Hi @PhotoRonin,

As the MAX function only takes 2 arguements, so you would need to do a number of nested MAX statements for 9 times in measure formula.

MaxDate =
MAX (
    MAX (
        MAX (
            MAX ( SELECTEDVALUE ( Sheet4[Product1] ), SELECTEDVALUE ( Sheet4[Product2] ) ),
            SELECTEDVALUE ( Sheet4[Product3] )
        ),
        SELECTEDVALUE ( Sheet4[Product4] )
    ),
    SELECTEDVALUE ( Sheet4[Product5] )
)

Here are two alternatives for your reference.

Add a custom column to show the max date within Power Query.

Locate to Query Editor mode. Selected all 10 product date columns and change their data type to Whole Number. Then, go to add Column -- Statistics--Maximum.

Change all date columns' data type to Date.

Another choice is to Unpivot table. Choose all your 1o columns and click "Unpivot Columns".

Return back to report view mode, use a Table visual to display above dataset.

Best regards,

Yuliana Gu