cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

Could anyone please help me out?

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Phase FinishDate = StartDate of next phase

Hi @Jolien ,

 

To insert an index column in power query firstly as below.

Capture.PNG

 

 

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

2.PNG

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

View solution in original post

Highlighted
Frequent Visitor

Re: Phase FinishDate = StartDate of next phase

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!

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

Re: Phase FinishDate = StartDate of next phase

Hi @Jolien ,

 

To insert an index column in power query firstly as below.

Capture.PNG

 

 

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

2.PNG

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

View solution in original post

Highlighted
Frequent Visitor

Re: Phase FinishDate = StartDate of next phase

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!

View solution in original post

Helpful resources

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