Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
PhotoRonin
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
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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
v-yulgu-msft
Microsoft Employee
Microsoft Employee

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.