Prior Date at each ID, Per Sequence

[Fro88er]

I am trying to figureout the days to redeploy a candidate. First I need the [dateBegin] from next placement to be on the same row as the prior placement.  (I will take a meaure or calc column solution). Below is what I have so far, but I can't get the next logic, Earlier? or other function to grab the right date.

This is a sample of one (269468) of many candidates in the table. 

Candidates: have unique IDs

PlacementID: are sequential in time, (I don't know how to use this to my benifit)

Thank you so much for assisting!

My attempt: 

(My Attempt) Redeploy Date =

CALCULATE(

MIN(FactPlacements[dateBegin]),

ALLEXCEPT(FactPlacements, FactPlacements[candidateID])

)

Desired Result in Yellow:

Sameple Data:

placementID	candidateID	dateBegin	ActualEndDate	RedeployDate (desired Result)
28595	269468	3/28/2016	8/12/2016	10/3/2016
30717	269468	10/3/2016	11/13/2016	11/14/2016
31144	269468	11/14/2016

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=CALCULATE(MIN(Data[dateBegin]),FILTER(Data,Data[candidateID]=EARLIER(Data[candidateID])&&Data[dateBegin]>EARLIER(Data[dateBegin])))

Hope this helps.

[Fro88er]

Thank you so much!  You made my weekend. 

Kind regards,

Mark

[Ashish_Mathur]

You are welcome.

[amitchandak]

@Fro88er , Try a new column like

new column = minx(filter(Table, [candidateID] =earlier([candidateID]), [dateBegin] > earlier([dateBegin])),[dateBegin])

[Fro88er]

I am getting this error.