Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
76 | |
75 | |
56 | |
38 | |
34 |
User | Count |
---|---|
99 | |
56 | |
51 | |
44 | |
40 |