How to define the value for each row in group based on another fields?

[Alyona_BI]

Hi guys!

I have this table with data: 

Item	Name	State	Date
A	Ted	New	01.08.2020
A	Kate	New	02.08.2020
A	Kate	Active	04.08.2020
A	Ann	Active	03.08.2020
A	Ann	Closed	05.08.2020
B	Kate	New	07.08.2020
B	Kate	Active	12.08.2020
B	Adrew	Active	13.08.2020
B	Peter	Active	14.08.2020
B	Kate	Closed	15.08.2020
C	Ted	New	05.08.2020
C	Ted	Active	15.08.2020
C	Andrew	Active	06.08.2020
C	Ann	Active	08.08.2020
C	Ann	Closed	16.08.2020

For each row within Item group i need to define Name as the one with "Active" State and minimum Date (for this Item). 

So the desired result should be: 

Item	Name	State	Date		First Name with Active State for Item
A	Ted	New	01.08.2020		Ann
A	Kate	New	02.08.2020		Ann
A	Kate	Active	04.08.2020		Ann
A	Ann	Active	03.08.2020		Ann
A	Ann	Closed	05.08.2020		Ann
B	Kate	New	07.08.2020		Kate
B	Kate	Active	12.08.2020		Kate
B	Adrew	Active	13.08.2020		Kate
B	Peter	Active	14.08.2020		Kate
B	Kate	Closed	15.08.2020		Kate
C	Ted	New	05.08.2020		Andrew
C	Ted	Active	15.08.2020		Andrew
C	Andrew	Active	06.08.2020		Andrew
C	Ann	Active	08.08.2020		Andrew
C	Ann	Closed	16.08.2020		Andrew

Could anyone help me to do this with help of DAX and calculated column? 

Thanks in advance! 

[Greg_Deckler · Accepted solution]

@Alyona_BI - See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __Previous = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Value])
RETURN
  __Current - __Previous

In your case, maybe:

First Name =
  VAR __Table = FILTER('Table',[Item]=EARLIER([Item]) && [State]="Active")
  VAR __MinDate = MINX(__Table,[Date])
RETURN
  MAXX(FILTER(__Table,[Date]=__MinDate),[Name])

[Alyona_BI]

@Greg_Deckler you are really super user! 🙂

It works! Many thanks! 

[Greg_Deckler]

@Alyona_BI - Glad to help! 🙂