Return Max Date of Grouped ID Filtered by Value in Another Column

[Anonymous]

Hi, folks,

First of all, this forum is amazing.

I want to create the "Max Date by Value" column, which includes the largest date for each ID group that has the value of 2.

Here is what I want such a table to look like.

ID	Date	Value	Max Date by Value
1	11/1/2015	4	5/1/2013
1	5/1/2014	5	5/1/2013
1	5/1/2013	2	5/1/2013
2	11/1/2012	1	Null
3	1/1/2013	2	1/1/2016
3	6/1/2010	5	1/1/2016
3	11/1/2016	4	1/1/2016
3	1/1/2016	2	1/1/2016
3	7/1/2017	7	1/1/2016

Here is the code I am playing with that is not working:

Max Term 2 Date =

var a =CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table', 'Table'[ID]), FILTER('Table', 'Table'[Value]=2))

var b =CALCULATE(MAX('Table'[Date]), FILTER('Table', 'Table'[ID]=EARLIER('Table'[ID])),'Table'[Date]=a)

Return

b

Thoughts on how to proceed?

Thanks!

[amitchandak · Accepted solution]

Try

Column

Max Date by Value = if([Value]=2, blank(),maxx(filter('Table', [ID]=earlier([ID])),[Date]))

Measure

Max Date by Value = calculate(max(Date),ALLEXCEPT('Table', 'Table'[ID]),[Value]<>2)
Max Date by Value = if(max([Value]=2),blank(),calculate(max(Date),ALLEXCEPT('Table', 'Table'[ID])))

[Anonymous]

Thank you, @amitchandak!

[Anonymous]

I think I just figured it out.

CALCULATE(MAX('Table'[Date]), FILTER('Table', 'Table'[ID]=EARLIER('Table'[ID])),'Table'[Value]=2)

I was overthinking it, but there may be a better way.