Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |