Find the biggest value in a table with conditions

[Anonymous]

Hi,

I need to find a solution to find the most recent date for all the lines who have the same ID.

Here is an exemple:

In the exemple below, I'm searching for a formula that will search in the column ID and Version all the lines with the same ID and the same version, then return "OK" for the one with the most recent date. 

ID1	Version	Date	Solution I want
123	v1	22/03/2020	KO
123	v1	23/09/2020	OK
123	v1	20/05/2020	KO
G0	v1	12/12/2020	OK
G0	v2	01/01/2020	KO
AV	v1	19/11/2020	KO
AV	v1	20/11/2020	OK
G0	v1	05/05/2020	KO
123	v1	02/02/2020	KO
123	v2	16/04/2020	KO
123	v2	17/04/2020	OK
G0	v2	24/09/2020	OK

I hope that it is clear.

Thank you for your help !

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Please try the below.

newest =
VAR newest =
CALCULATE ( MAX ( Data[Date] ), ALLEXCEPT ( Data, Data[ID1], Data[Version] ) )
RETURN
IF ( MAX ( Data[Date] ) = newest, "OK", "KO" )

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[Anonymous]

Thank you Jihwan. It didn't work at the beginning but when I deleted the max() in your last line, it worked ! Thank you for your help ! 

[DataZoe]

@Anonymous you could try this measure:

Solution =
VAR MaxDate =
CALCULATE ( MAX ( 'Table'[Date] ), REMOVEFILTERS ( 'Table'[Date] ) )
RETURN
IF ( SELECTEDVALUE ( 'Table'[Date] ) = MaxDate, "OK", "KO" )

[Anonymous]

Hi DataZoe,

Thank you for you help but it didn't work. It doesn't filter the colums ID and versions to find the most recent dates with the same ID/versions