Forum Discussion
Helper ICan' assign table elements to variables
Helper ITo rephrase mi initial input, the result of this exercise is to show which dates are overlaping for the same task so the next task can't start during the previous. Basically i want to have another column generated where it suggests the start date of the overlaping task is in form of finished date of previous task + 1 day.
Community ChampionHi IgorKaradzic
So one of output is which date is not overlaping (not task for that date active or similar...)? It would be view per date?
How you define overlaping? Two and more distinct ID per task or ...?
What is desired output for table above (in than one or more new columns)
- IgorKaradzic
Got it, thank you for your help regardless. Sorry i wans't able to convey my message in simpler way.
- IgorKaradzic
ID Task Responsible
Task
Planned Start Date
Planned Finish Date
1
Task1
13.3.2024.
23.3.2024.
2
Task2
23.3.2024.
25.3.2024.
2
Task3
25.3.2024.
1.4.2024.
2
Task4
25.3.2024.
29.3.2024.
2
Task5
29.3.2024.
7.4.2024.
2
Task6
7.4.2024.
10.4.2024.
Overlaping is where one employee works on different tasks simultaneously as you can see from this table.
- some_bih
Hi IgorKaradzic
I understand that Task colum and column Planned Finish Date are critical input for new column NextDateForTask with definition below.
It could be that another column is key inputs as your simple example do not have some expected output.
Check it with your real data.
NextDateForTask =--assuming Task is column criteria for max dateCALCULATE(MAX(Sheet1[Planned Finish Date]),ALLEXCEPT(Sheet1,Sheet1[Task]))+1--adding one single date for start next taskOutput
- IgorKaradzic
Hello, sorry for not answering earlier but this is still not the solution i am looking for. It's close but not it. Thank you for your help.
- some_bih
Hi IgorKaradzic
I expected your last answer as I did not know what inputs are and at which level to provide calculated column works as you want.
- IgorKaradzic
Like i said in the last reply, you were close. Instead of increasing next start date by +1 for every row separately, what should be done is based on previous row end date increase current row start date by previous row end date + 1.
- some_bih
Hi IgorKaradzic
Check definition for column PreviousRowEndDate
PreviousRowEndDate =//calculation based on ID Task and Finish DateVAR __idtask_responsible=Sheet1[ID Task Responsible]VAR __task=Sheet1[Task]VAR __date_of_planned_finish=Sheet1[Planned Finish Date]VAR __calculation =CALCULATE(MAX(Sheet1[Planned Finish Date]),FILTER(Sheet1,Sheet1[ID Task Responsible]=__idtask_responsible && Sheet1[Planned Finish Date] < __date_of_planned_finish))VAR Result= IF(__calculation = BLANK(), Sheet1[Planned Finish Date], __calculation+1)//in Result adding 1 dayRETURN Result - IgorKaradzic
I know you are trying your best to help me but looks like i don't know how to explain the end goal of this so i will send you table which i want as a result and yu tell me if it's possible to do it. We compare each 2 tasks, for example task 1 with task 2 and nothing changes etween them because their id's are different, task 1 with task 2, same id's and we can see that task 1 end date is same as task 2 start date so we need to move task 2 start date by task 1 end date + 1 and also move task 2 end date too. And we do that for task 2 with task 3 etc.
ID Task Responsible
Task
Planned Start Date
Planned Finish Date
1
Task1
13.3.2024.
23.3.2024.
2
Task2
23.3.2024.
25.3.2024.
2
Task3
25.3.2024.
1.4.2024.
2
Task4
25.3.2024.
29.3.2024.
2
Task5
29.3.2024.
7.4.2024.
2
Task6
7.4.2024.
10.4.2024.
Actual Start Date
Actual Finish Date
13.3.2024.
23.3.2024.
23.3.2024.
25.3.2024.
26.3.2024.
2.4.2024.
3.4.2024.
7.3.2024.
8.4.2024.
17.4.2024.
18.4.2024.
21.4.2024.
- some_bih
Hi IgorKaradzic
I could not follow your logic. Maybe somebody else could help you