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:
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:
I’ve been desperately looking for formulas and calculations but haven’t found any solution to date.
Could anyone please help me out?
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.
VAR ind = 'Table'[Index] + 1
MAX ( 'Table'[StartDate] ),
FILTER ( ALL ( 'Table' ), 'Table'[Index] = ind )
View solution in original post
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:
However, your hint of using VAR did put me in the right direction.
I've solved it by creating this column:
VAR SelectedProjectID = 'Tasks'[ProjectId]
"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!
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!