Creating Interconnected Date Columns by Priority Index for Gantt Chart

[pinar1]

Hello, 

I am new to the BI and I have searched the Forum to see of I can find a solution. But I couldn't find. So It would be super if somebody could help me with my question. I need to prepare a @Gantt for work but lack some info:  

I have the information below: 

Each Resource has different tasks with a priority sequence.

Resource	Task	Priority	Start Date	Duration (day)	End Date
Florida	sushi	1		2
Florida	pasta	2		4
Miami	burito	1		1
Miami	nachos	2		2
Miami	pizza	3		3
Miami	steak	4		1

What I need is to create a Start Date column, which will automatically return Today's Date for Priority=1  for each resource and then adds the Duration for End Date column. 

After that, the Task with Priority=2 will have a Start Date equal to the End Date of Priority=1 Task. Then Priority=3 Task will have Start Date equal to End Date of Priority=2 Task. 

So I want to see something like this:

Resource	Task	Priority	Start Date	Duration (day)	End Date
Florida	sushi	1	8/30/2020	2	9/1/2020
Florida	pasta	2	9/1/2020	4	9/5/2020
Miami	burito	1	8/30/2020	1	8/31/2020
Miami	nachos	2	8/31/2020	2	9/2/2020
Miami	pizza	3	9/2/2020	3	9/5/2020
Miami	steak	4	9/2/2020	1	9/3/2020

It would be so much appreciated! 

[Fowmy · Accepted solution]

@pinar1 

Add the following two columns to your table by going to theTable, New Column:

Start Date

Start Date = 
var T = TODAY()
VAR P = [Priority ]
RETURN
SWITCH( TRUE(),
P=1, T,
CALCULATE( T + SUM(Table4[Duration (day)]), Table4[Priority ] < P, ALLEXCEPT(Table4,Table4[Resource]))  
)

End Date

End Date = [Start Date] +[Duration (day)]

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn