Lead and Lag Help

[ConnorH]

I am getting really close to solvbing my issue but there is just one more peice I can not figure out. I was able to pull the Latest date where ProjectID=ProjectID and Status=Status. I still do not know how to set this equation up to only keep one value per status per project ID. So for example In the first row of the table below the ProjectID 34631 with the status 2.1, modified 6/1/20  [Last status date] should be blank because there is a later date with the same project ID and Status.

ProjectID	Status Extract	Modified	Last Status Date (New column)
34631	2.1	6/1/20
34631	2.1	6/4/20	6/4/20
34631	4.0	6/20/20	6/20/20

"Last status date =

var _ProjectID = 'Status'[ProjectID]

var _Status = 'Status'[Status Extract]

Var _StatusDate = 'Status'[Modified]

var _LaterDate = MAXX(FILTER('Status','Status'[ProjectID] = _ProjectID && 'Status'[Status Extract] = _Status), 'Status'[Modified])

Return _LaterDate"

this equation will make the table like this and it wont leave the values blank.

ProjectID	Status Extract	Modified	Last Status Date (New column)
34631	2.1	6/1/20	6/4/20
34631	2.1	6/4/20	6/4/20
34631	4.0	6/20/20	6/20/20

[amitchandak]

@ConnorH , Try a new column like

maxx(filter(Table, [ProjectID] =earlier([ProjectID]) && [Status Extract] =earlier([Status Extract])),[Modified] )

[wdx223_Daniel · Accepted solution]

Last status date =
var _ProjectID = 'Status'[ProjectID]
var _Status = 'Status'[Status Extract]
Var _StatusDate = 'Status'[Modified]
var _LaterDate = MAXX(FILTER('Status','Status'[ProjectID] = _ProjectID && 'Status'[Status Extract] = _Status), 'Status'[Modified])

Return IF(_LaterDate=_StatusDate,_LaterDate)