Reply
Regular Visitor
Posts: 27
Registered: ‎03-26-2017

Team overload with Due date

Hi, @v-jiascu-msft

 

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.

Screenshot_4.png

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 timeSmiley Embarassed

 

Link to Pbix file:

https://drive.google.com/open?id=1zjL-1QucssmAwd9zhmvhwuw2yI9Hv4Cw

Highlighted
Community Support Team
Posts: 5,429
Registered: ‎09-21-2016

Re: Team overload with Due date

Hi @rimgri,

 

Sorry, I am not very clear about your requirement. Would you please illustrate the expected result with some simple sample data?

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor
Posts: 27
Registered: ‎03-26-2017

Re: Team overload with Due date

Ofcourse @v-yulgu-msft

 

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))

Screenshot_5.png

For ADASLI the task's should be spited like that:

Screenshot_6.png

 

I hope it's more clear what I want, if you have any questions don't hesitate to ask.