Forum Discussion
Iterating and comparing rows
I have a table of tasks assigned to different people, with set due dates, hours and priorities. I have previously processed these (in Visual Studio) to determine an order of works. This was done by
- Grouping tasks by person
- Ranking by due date
- Determining projected completion dates, based on the sum of the hours of tasks ranked above them, then
- Iterating the rankings from first to last and checking whether the due date was being met
- If yes no action was taken
- If no it was checked whether this task was a higher priority than the task ranked above, if so its rank was switched with the one above and projected end dates recalculated until it was either meeting due date or was not a higher priority than the task above (essentially moving it up the rankings).
I am new to PowerBI and am trying to follow the same logic but can't figure out how to iterate, with reference to other rows. Can anyone assist with the best way to approach this?
Sample data
| AssignedToID | Due Date | Priority (999 = highest) | WorkingDaysRemaining |
| 5 | 1/08/2022 | 200 | 5 |
| 6 | 1/08/2022 | 100 | 5 |
| 7 | 5/08/2022 | 100 | 7 |
| 7 | 10/08/2022 | 100 | 4 |
| 6 | 13/08/2022 | 500 | 5 |
| 5 | 15/08/2022 | 900 | 10 |
| 6 | 15/08/2022 | 500 | 5 |
| 5 | 20/08/2022 | 500 | 5 |
| 7 | 30/08/2022 | 900 | 2 |
Desired result
| AssignedToID | Due Date | Priority (999 = highest) | WorkingDaysRemaining | Rank | ProjectedEndDate | Ontime |
| 5 | 15/08/2022 | 900 | 10 | 1 | 15/08/2022 | Y |
| 5 | 20/08/2022 | 500 | 5 | 2 | 22/08/2022 | N |
| 5 | 1/08/2022 | 200 | 5 | 3 | 29/08/2022 | N |
| 6 | 13/08/2022 | 500 | 5 | 1 | 8/08/2022 | Y |
| 6 | 15/08/2022 | 500 | 5 | 2 | 15/08/2022 | Y |
| 6 | 1/08/2022 | 100 | 5 | 3 | 20/08/2022 | N |
| 7 | 5/08/2022 | 100 | 7 | 1 | 16/08/2022 | N |
| 7 | 10/08/2022 | 100 | 4 | 2 | 22/08/2022 | N |
| 7 | 30/08/2022 | 900 | 2 | 3 | 24/08/2022 | Y |
8 Replies
Hi DebbieK ,
I am honestly confused with the sample and expected results provided. The due dates don't match as well as the priorities. For ID5, the dates are all 1/8 in the sample data but they're different in the expected result except for one. The priority numbers are also different - {900,500,100} vs {900,500,200}
Hi Danextian - thank you, and sorry for the mistake in the sample data, one of the due dates were wrong. I have now corrected this.
Note in the sample data it is sorted by due date, in the output data it is grouped by ID and then sorted by rank.
Hello,
Where can I find the hours?
Determining projected completion dates, based on the sum of the hours of tasks ranked above them
Also, how is the projected end date computed? I am assuming that it is 7 (x rank-1) days from rank1 project.
