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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
paquetteth
Frequent Visitor

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

@paquetteth

 

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.

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!

@paquetteth

 

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

@paquetteth

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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