Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
@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
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!
@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
@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!
So just to be clear you want to create a Table Visualization that list each product and only the max date for each product?
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
69 | |
68 | |
40 | |
29 | |
26 |
User | Count |
---|---|
88 | |
49 | |
45 | |
38 | |
37 |