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
Anonymous
Not applicable

MAX/IF combination to pull date based on product number

In Excel, I am able to use the MAX/IF array function to look at Cell A2 (product number), look through all of column A for matching product numbers, and look at column B to find the max date for that serial number.  This would go in cell C2 and the function would repeat for each cell in C looking next at A3, A4, A5, etc. to pull the max date.  Quick example - The max DATE for each PRODUCT is in RED:

 

 

 

PRODUCTDATEMAX DATE
ABC12/31/201612/31/2017
DEF1/1/201412/31/2017
ABC5/31/201512/31/2017
XYZ6/31/20158/1/2017
DEF7/21/201212/31/2017
XYZ4/1/20118/1/2017
DEF5/31/201612/31/2017
DEF12/31/201712/31/2017
ABC12/31/201712/31/2017
DEF1/1/201512/31/2017
XYZ8/1/20178/1/2017
ABC9/31/201612/31/2017
XYZ7/31/20148/1/2017
DEF1/1/201512/31/2017
DEF3/1/201112/31/2017

 

I have been hammering away trying to reproduce in power query/power BI and have not been successful.  I am new to the software and community (though I have been using power query through Excel for a year or so now) so excuse any ignorance in my post! Thanks in advance for any help!

 

**UPDATE** - I was able to recreate using a measure but I cannot figure out how to get the Max Date loaded into a new column from the measure.  

 

Tom

1 ACCEPTED SOLUTION

@Anonymous

 

hi, Use this measure:

 

ltdate.png

 

Or if you wanna show only latest date for each Product:

 

In a table visual add Product and Date. And Select Latest in Date Column

 

lteas.png




Lima - Peru

View solution in original post

5 REPLIES 5
parry2k
Super User
Super User

I think you can achieve by creating new measure with following expression:

 

MaxDate = MAX(Table1[Date])

I didn't tested it but I believe it willl work.

 

Thanks,

P

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

Thanks for the reply!  I was able to use a similar measure and then create a visualization that gave me the latest end date for each product.  I should elaborate - I would like to use that measure to create a new column that lists the max end date as I showed in my example from Excel.

 

Thanks!

@Anonymous

 

hi, Use this measure:

 

ltdate.png

 

Or if you wanna show only latest date for each Product:

 

In a table visual add Product and Date. And Select Latest in Date Column

 

lteas.png




Lima - Peru
Sean
Community Champion
Community Champion

@Anonymous

 

You can use this Measure to get only the latest dates for each product

Then you won't need the Visual Level Filters

 

Measure =
IF (
    HASONEVALUE ( 'Table'[PRODUCT] ),
    CALCULATE ( MAX ( 'Table'[DATE] ), VALUES ( 'Table'[PRODUCT] ) ),
    BLANK ()
)

Latest Date per Product.png

 

Good Luck! Smiley Happy

Sean
Community Champion
Community Champion

So just to be clear you want to create a Table Visualization that list each product and only the max date for each product?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors