Forum Discussion
ConnorH
Microsoft Employee
Microsoft EmployeeLead and Lag Help
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 |
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)
2 Replies
- wdx223_Daniel
Community Champion
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) - amitchandak
Super User
ConnorH , Try a new column like
maxx(filter(Table, [ProjectID] =earlier([ProjectID]) && [Status Extract] =earlier([Status Extract])),[Modified] )
