cancel
Showing results 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

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:

 PRODUCT DATE MAX DATE ABC 12/31/2016 12/31/2017 DEF 1/1/2014 12/31/2017 ABC 5/31/2015 12/31/2017 XYZ 6/31/2015 8/1/2017 DEF 7/21/2012 12/31/2017 XYZ 4/1/2011 8/1/2017 DEF 5/31/2016 12/31/2017 DEF 12/31/2017 12/31/2017 ABC 12/31/2017 12/31/2017 DEF 1/1/2015 12/31/2017 XYZ 8/1/2017 8/1/2017 ABC 9/31/2016 12/31/2017 XYZ 7/31/2014 8/1/2017 DEF 1/1/2015 12/31/2017 DEF 3/1/2011 12/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
Community Champion

@Anonymous

hi, Use this measure:

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

Lima - Peru
5 REPLIES 5
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!

Community Champion

@Anonymous

hi, Use this measure:

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

Lima - Peru
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 ()
)```

Good Luck!

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?

Announcements

#### 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 Monthly Update - June 2024

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

#### 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
Top Kudoed Authors