topic Re: Lead and Lag Help in DAX Commands and Tips

Lead and Lag Help

ConnorH — Tue, 03 Nov 2020 20:12:13 GMT

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

Re: Lead and Lag Help

wdx223_Daniel — Wed, 04 Nov 2020 00:34:47 GMT

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)

Re: Lead and Lag Help

amitchandak — Wed, 04 Nov 2020 01:57:12 GMT

ConnorH , Try a new column like

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