MAX/IF combination to pull date based on product number

[Anonymous]

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

[parry2k]

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

[Anonymous]

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!

[Vvelarde · Accepted 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

[Sean]

@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!

[Sean]

So just to be clear you want to create a Table Visualization that list each product and only the max date for each product?