12-06-2018 08:26 AM
I got stuck with Due Date question regarding this issue: https://community.powerbi.com/t5/Desktop/Dynamic-table-to-calculate-team-overload/td-p/574472
I put all calculations in columns (to crearly understand algorythms).
What I whanted to do, is to evaluate Due date for those Issues that hasn't Start Date. During the spliting if there are any hours left after the due date, than put everything in this date.
I tried in two ways: Solution 2 ir Solution 3 (Solution is original (yours created))
For Solution 2 I just created a formula:
Solution2 = IF([Due Date]=[Date];[Stock];IF([Due Date]<[Date];0;[Solution]))
It works fine, but there is one issue. When it's a big overload (task's Due Date is in 2019-01-31, and his task without evaluating Due Date will be splitting just after two or three month) it just put all hours in Due Date - Date, ignoring, that there where vacant days with efective hours before the date.
For Solution 3 I created:
accumulateRemaining_due - sums task's Remainig Estimate, for the assignee and where Index is lower than it's, and bring just those task wich has avaliable Due date
accumulateToLast_due = accumulateRemaining_due - Remainig estimate
Solution 3 = like Solution, but I use accumulateToLast_due and accumulateRemaining_due
Everything works by the logic, but it doesn't split task in quite good order and way, because accumulateRemaining_due is less than it should be. Bu I can't figure out how I shoud evalute the Due date tasks that is over.
Could you help me to figure out how can I have Due Date envolved and got a good spit for the tasks?
I really appreciate your help one more time
Link to Pbix file:
12-10-2018 01:43 AM
Sorry, I am not very clear about your requirement. Would you please illustrate the expected result with some simple sample data?
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
12-10-2018 02:59 AM
The main task was to split all JIRA task's Remanaining Estimate hours by the time, evaluating Effective worker hours for the day . Everything is working fine, but I noticed if there are a big overload for a assignee that formula splits hours not looking at Due date. And it shows for how long worker will have job, but don't show what days/week/ month he will have overload. For this, we need to evaluate the Due date:
- if task have a due date 2018 12 31, there won't be any splitted hours after this date. All hours left for this day will be put in Due Date day (this is solved by Solutinion 2)
- and if there is Effective hours till this day, it will spit hours (this is the problem)
If you can look at Tasks sheet in pibx file, the issue like GND-476, IND-378, VND-237, VND-240.
They have all Remainiging Estimate hours in Due Date date, but here is Effective hours before thease dates, so thease hours should be spited for thease dates.
For Example IND-378 (Remaining Estimate hours - 8,25, Due Date -2018-12-11).
We See that there is 2018-12-10 overldoad - 0 hours (it'hasn't any task's spited for this date), so it should put some of IND-378 hours, for this date.
For IND-378 it should be:
- 2018-12-10 - 5 hours (max of Effective hours)
- 2018-12-11 - 3,25 (left hours for this task (8,25-5 h))
For ADASLI the task's should be spited like that:
I hope it's more clear what I want, if you have any questions don't hesitate to ask.