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.
Hi all,
My problem is fairly easy, however it proves to be a pain to translate into Power Query.
I have a schedule where different milestones mark the start of different project phases (The start of phase 2 marks the finish of phase 1).
This results in the table as follows:
ProjectId | ProjectName | TaskName | ProjectPhase | StartDate |
0001 | Project 1 | Milestone1 | Phase1 | 10/04/2019 |
0001 | Project 1 | Milestone2 | Phase2 | 18/06/2019 |
0001 | Project 1 | Milestone3 | Phase3 | 11/05/2020 |
… |
|
|
|
|
I’d like to add a column to this table with the Finish date, which is the start date of the next phase.
Basically I want my table to like this:
ProjectId | ProjectName | TaskName | ProjectPhase | StartDate | FinishDate |
0001 | Project 1 | Milestone1 | Phase1 | 10/04/2019 | 18/06/2019 |
0001 | Project 1 | Milestone2 | Phase2 | 18/06/2019 | 11/05/2020 |
0001 | Project 1 | Milestone3 | Phase3 | 11/05/2020 | 07/12/2020 |
… |
|
|
|
|
|
I’ve been desperately looking for formulas and calculations but haven’t found any solution to date.
Could anyone please help me out?
Solved! Go to Solution.
Hi @Jolien ,
To insert an index column in power query firstly as below.
After that, to creat a calculated column based on index like this.
Column = VAR ind = 'Table'[Index] + 1 RETURN CALCULATE ( MAX ( 'Table'[StartDate] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] = ind ) )
Hi @v-frfei-msft ,
Thank you for your reply. Your solution works, but only if the data is nicely sorted.
I'm afraid I oversimplified the data in my example.
What I have is more like this:
ProjectId | ProjectName | TaskName | ProjectPhase | StartDate |
0001 | Project 1 | Milestone1 | Phase1 | 10/04/2019 |
0001 | Project 1 | Milestone2 | Phase2 | 18/06/2019 |
0001 | Project 1 | Milestone3 | Phase3 | 11/05/2020 |
0002 | Project 2 | Milestone2 | Phase2 | 18/06/2019 |
0002 | Project 2 | Milestone1 | Phase1 | 10/04/2019 |
0002 | Project 2 | Milestone3 | Phase3 | 11/05/2020 |
0003 | Project 3 | Milestone1 | Phase1 | 10/04/2019 |
0003 | Project 3 | Milestone3 | Phase3 | 11/05/2020 |
... |
|
|
|
|
However, your hint of using VAR did put me in the right direction.
I've solved it by creating this column:
PhaseFinishDate = VAR SelectedProjectID = 'Tasks'[ProjectId] RETURN SWITCH( 'Tasks'[ProjectPhase]; "Phase1";
CALCULATE(
MAX( 'Tasks'[StartDate] );
FILTER(
FILTER( ALL( 'Tasks' ); 'Tasks'[ProjectId] = SelectedProjectID );
'Tasks'[ProjectPhase] = "Phase2"
)
); "Phase2";
CALCULATE(
MAX( 'Tasks'[StartDate] );
FILTER(
FILTER( ALL( 'Tasks' ); 'Tasks'[ProjectId] = SelectedProjectID );
'Tasks'[ProjectPhase] = "Phase3"
)
); "Phase3";
CALCULATE(
MAX( 'Tasks'[TaskStartDate] );
FILTER(
FILTER( ALL( 'Tasks' ); 'Tasks'[ProjectId] = SelectedProjectID );
'Tasks'[TaskName] = "End of project"
)
)
)
Thanks for the help!
Hi @Jolien ,
To insert an index column in power query firstly as below.
After that, to creat a calculated column based on index like this.
Column = VAR ind = 'Table'[Index] + 1 RETURN CALCULATE ( MAX ( 'Table'[StartDate] ), FILTER ( ALL ( 'Table' ), 'Table'[Index] = ind ) )
Hi @v-frfei-msft ,
Thank you for your reply. Your solution works, but only if the data is nicely sorted.
I'm afraid I oversimplified the data in my example.
What I have is more like this:
ProjectId | ProjectName | TaskName | ProjectPhase | StartDate |
0001 | Project 1 | Milestone1 | Phase1 | 10/04/2019 |
0001 | Project 1 | Milestone2 | Phase2 | 18/06/2019 |
0001 | Project 1 | Milestone3 | Phase3 | 11/05/2020 |
0002 | Project 2 | Milestone2 | Phase2 | 18/06/2019 |
0002 | Project 2 | Milestone1 | Phase1 | 10/04/2019 |
0002 | Project 2 | Milestone3 | Phase3 | 11/05/2020 |
0003 | Project 3 | Milestone1 | Phase1 | 10/04/2019 |
0003 | Project 3 | Milestone3 | Phase3 | 11/05/2020 |
... |
|
|
|
|
However, your hint of using VAR did put me in the right direction.
I've solved it by creating this column:
PhaseFinishDate = VAR SelectedProjectID = 'Tasks'[ProjectId] RETURN SWITCH( 'Tasks'[ProjectPhase]; "Phase1";
CALCULATE(
MAX( 'Tasks'[StartDate] );
FILTER(
FILTER( ALL( 'Tasks' ); 'Tasks'[ProjectId] = SelectedProjectID );
'Tasks'[ProjectPhase] = "Phase2"
)
); "Phase2";
CALCULATE(
MAX( 'Tasks'[StartDate] );
FILTER(
FILTER( ALL( 'Tasks' ); 'Tasks'[ProjectId] = SelectedProjectID );
'Tasks'[ProjectPhase] = "Phase3"
)
); "Phase3";
CALCULATE(
MAX( 'Tasks'[TaskStartDate] );
FILTER(
FILTER( ALL( 'Tasks' ); 'Tasks'[ProjectId] = SelectedProjectID );
'Tasks'[TaskName] = "End of project"
)
)
)
Thanks for the help!
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 |
---|---|
101 | |
52 | |
21 | |
12 | |
11 |