Earliest Value based on filter/criteria

[ttseng]

Hi, 

I have a dataset as follows:

ID	Date	Success	Score
123	11/19/2020	0	5
123	11/20/2020	1	6
123	11/21/2020	1	2
122	11/19/2020	0	8
122	11/20/2020	1	9
133	11/19/2020	1	1
133	11/20/2020	1	3
144	11/19/2020	0	4

You'll notice that ID is a column with repeats with each row tracking the date and success and score. 

What I'm looking for is to be able to return the earliest known score for each unique ID where success is = 1. I would like to be able to populate the following (earliest_success_score). 

ID	Date	Success	Score	Earliest_success_score
123	11/19/2020	0	5	null
123	11/20/2020	1	6	6
123	11/21/2020	1	2	null
122	11/19/2020	0	8	null
122	11/20/2020	1	9	9
133	11/19/2020	1	1	1
133	11/20/2020	1	3	null
144	11/19/2020	0	4	null

Thanks!

p.s. it's possible that there could be multiple submissions for a single day and the data has a time stamp in additiona to the date so the solution would look at earlist date and time (keeping the sample data simpler for ease of my understanding).

[amitchandak · Accepted solution]

@ttseng , Create a new column like

Column = var _1 = minx(FILTER(Data, [ID] =EARLIER([ID]) && Data[Success] = 1),Data[Date])
return 
if(_1 =[date] ,minx(FILTER(Data, [ID] =EARLIER([ID]) && Data[Success] = 1 && [Date] =_1),[Score]),BLANK())

[ttseng]

thanks @amitchandak ! your solution worked great.