Set a measure equal to a filtered value of a different subset of that data???

[Anonymous]

I'm not even 100% sure how to explain what I want to do here, so if there is a less complicated way to get where I'm going, PLEASE chime in. But essentially I have data that looks like this:

Project	Decision Point	Target Date	Actual Date
1	A	1/14/2019	2/11/2019
1	B	4/26/2019	5/2/2019
1	C	4/28/2020	4/16/2020
1	D	10/12/2020
1	E	10/27/2020
2	...

I want to make a gantt chart of this data where I mostly use the Actual date of the decision point PRIOR as the start Date and the Actual Date of the Decision Point as the End Date, unless there is no actual date, then I want use the Target Date. (Or unless it's DP A where I have to use the Target date as the start date right now because we haven't been capturing a start date of projects yet)

So in this case, I would want the Date Variables to be as listed below. Is there a good way for me to be able to create these Start and End Date Variables?

Project	Decision Point	Start Date	End Date
1	A	1/14/2019	2/11/2019
1	B	2/11/2019	5/2/2019
1	C	5/2/2019	4/16/2020
1	D	4/16/2020	10/12/2020
1	E	10/12/2020	10/27/2020

[lbendlin · Accepted solution]

Source table "Gantt":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYkN9QxN9IwNDSyDbSN/QEMKO1YGocAJioLQZTIWpvhGqAmeIAgugqJEBmG1oBmHDVLiALDHQNzSCKVGAS7lCpIzMkaRiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Project = _t, #"Decision Point" = _t, #"Target Date" = _t, #"Actual Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Target Date", type date}, {"Actual Date", type date}})
in
    #"Changed Type"

Calculated columns (note that Start Date depends on End Date so End Date needs to be defined first)

End Date = COALESCE(Gantt[Actual Date],Gantt[Target Date])

Start Date = 
var d = Gantt[Decision Point]
return if(d="A",Gantt[Target Date],CALCULATE(max(Gantt[End Date]),all(Gantt),Gantt[Decision Point]<d))

Result:

Note: this does not include the project filter. I leave that exercise up to you.