Phase FinishDate = StartDate of next phase

[Jolien]

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?

[v-frfei-msft · Accepted 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 )
    )

[v-frfei-msft · Accepted 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 )
    )

[Jolien · Accepted solution]

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!