cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Phase FinishDate = StartDate of next phase

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support

## Re: Phase FinishDate = StartDate of next phase

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 )
)
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Frequent Visitor

## Re: Phase FinishDate = StartDate of next phase

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 =
RETURN
SWITCH(

Thanks for the help!

2 REPLIES 2
Highlighted
Community Support

## Re: Phase FinishDate = StartDate of next phase

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 )
)
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Highlighted
Frequent Visitor

## Re: Phase FinishDate = StartDate of next phase

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 =
RETURN
SWITCH(

Thanks for the help!

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors