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:
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.
Solved! Go to Solution.
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.
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 () )
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.