Return values from conditional column within each group

[Quynhtran]

Hello everybody,

I would like to create new column [Name Revised], which updates the correct name for each ID. For example, if there are in the same ID, the correct name is the name that have the earliest date. 

Name	Date	Value	ID	Name Revised
A	12/1/2021	10	123456	A
B	12/3/2021	-10	123456	A
C	12/1/2021	20	123789	C
D	12/5/2021	-20	123789	C

I have tried many approaches but they still doesnt work (TOPN, FIRSTNONBLANK, creating summarize table then LOOKUPVALUES, ..). 

Should you have any idea to solve this issue, please share with me.

Many Thanks.

Quynh Tran.

[Jihwan_Kim · Accepted solution]

Name Revised CC =
VAR _currentID = Data[ID]
VAR _mindate =
CALCULATE ( MIN ( Data[Date] ), FILTER ( Data, Data[ID] = _currentID ) )
RETURN
CALCULATE (
DISTINCT ( Data[Name] ),
FILTER ( Data, Data[ID] = _currentID && Data[Date] = _mindate )
)

Link to the pbix file 

[Quynhtran]

Thanks Jihwan,

It works perfectly.