cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Jolien Frequent Visitor
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
Community Support Team
Community Support Team

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

Jolien Frequent Visitor
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
Community Support Team
Community Support Team

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

Jolien Frequent Visitor
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
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors