Iterating and comparing rows

[DebbieK]

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

1. Grouping tasks by person
2. Ranking by due date
3. Determining projected completion dates, based on the sum of the hours of tasks ranked above them, then
4. Iterating the rankings from first to last and checking whether the due date was being met
5. If yes no action was taken
6. 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

[danextian]

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} 

[DebbieK]

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. 

[danextian]

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.

[DebbieK]

Hi danextian - thanks for your help. 

The hours = 8 hours x number of working days. 

The projected completion date is the end of the previously ranked project + working days. For example ID 5, top ranked project finishes on 15/08/22. ID 5 second ranked project then has a completion date of 15/8/22 + 5 working days being 22/8/22 (due to weekend days). 

[danextian]

I am confused how the rank is calculated. For ID5, it appears that they're ranked by priority. For 7, they are ranked by Due Date.

[DebbieK]

Hi danextian,

The logic is to rank by due date, then check if a higher prioirity task is not ontime and if so promote it up the ranks.

For ID 7 they are ranked by due date, and the higher priority task is on time, so no changes are needed.

I've played out the example process for ID 5 below.

STEP 1 - Sort by due date 

AssignedToID	Due Date	Priority (999 = highest)	WorkingDaysRemaining
5	1/08/2022	200	5
5	15/08/2022	900	10
5	20/08/2022	500	5
6	1/08/2022	100	5
6	13/08/2022	500	5
6	15/08/2022	500	5
7	5/08/2022	100	7
7	10/08/2022	100	4
7	30/08/2022	900	2

STEP 2 - Iterate and check whether on time, promote if needed. 

EG.

Row 2, ONTIME = N, so check whether Row 2 is a higher priority than Row 1. 

900 > 200 so move Row 2 up rank. 

AssignedToID	Due Date	Priority (999 = highest)	WorkingDaysRemaining	ProjectedEndDate	Ontime
5	1/08/2022	200	5	8/08/2022	N
5	15/08/2022	900	10	22/08/2022	N
5	20/08/2022	500	5	29/08/2022	N
6	1/08/2022	100	5	8/08/2022	N
6	13/08/2022	500	5	15/08/2022	N
6	15/08/2022	500	5	22/08/2022	Y
7	5/08/2022	100	7	10/08/2022	N
7	10/08/2022	100	4	16/08/2022	N
7	30/08/2022	900	2	18/08/2022	Y

Then EG 

Row 3, ONTIME = N, so check whether Row 3 is higher priority than Row 2.

Priority 500> 200, so move Row 3 up rank

ONTIME still = No

Check whether higher priority than Row 1

Priority 500 < 900 so do not move higher.

AssignedToID	Due Date	Priority (999 = highest)	WorkingDaysRemaining	ProjectedEndDate	Ontime
5	15/08/2022	900	10	15/08/2022	Y
5	1/08/2022	200	5	22/08/2022	N
5	20/08/2022	500	5	29/08/2022	N
6	1/08/2022	100	5	8/08/2022	N
6	13/08/2022	500	5	15/08/2022	N
6	15/08/2022	500	5	22/08/2022	Y
7	5/08/2022	100	7	10/08/2022	N
7	10/08/2022	100	4	16/08/2022	N
7	30/08/2022	900	2	18/08/2022	Y

Finished iteration of ID5 

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	1/08/2022	100	5		8/08/2022	N
6	13/08/2022	500	5		15/08/2022	N
6	15/08/2022	500	5		22/08/2022	Y
7	5/08/2022	100	7		10/08/2022	N
7	10/08/2022	100	4		16/08/2022	N
7	30/08/2022	900	2		18/08/2022	Y

[danextian]

In step 2, how would one know whether a project is on-time or not? Also in row 2, 15/8 + 10 workdays shouldn't that be 29/8?

[DebbieK]

If ProjectedEndDate is on or before DueDate than the project is ontime

Take "TODAY" as 1 August. For example in Step 2 (screenshot below)

- Row one is 5 days and so ProjectedEndDate is 8 August

- Row two is 10 days and so ProjectedEndDate is 10 working days AFTER the end date of Row 1 (so 8 August + 10wd = 22 August)