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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
Employee
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
Employee
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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.