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

Get MAX value of dates across multiple columns

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.

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

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] )
)

1.PNG

 

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.

2.PNG3.PNG

 

Change all date columns' data type to Date.

4.PNG

 

 

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

5.PNG6.PNG

 

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

7.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
Microsoft
Microsoft

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] )
)

1.PNG

 

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.

2.PNG3.PNG

 

Change all date columns' data type to Date.

4.PNG

 

 

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

5.PNG6.PNG

 

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

7.PNG

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors