Forum Discussion
Dynamic table to calculate team overload
Hi everyone,
I'm not advanced user in Power BI, but I got a request from my manager, that I don't know if it's even possible to do that in Power BI.
I have to split team overload demending on team member task and dates in this task. Something like that:
I have three tables:
- Jira data table with asignee (people), task ID, start date, due date, estimate-Worked
- Effective hours table - people effective hours
- Calendar table
I'm thinking to create dynamic table (if it's possible): in rows- assignee, colums - today +90 days, the values should be calculated after refresh by the task dates:
- If task date have [Start date] and [Ending date], than all [Remaining Estimate] hours should be splited by [Hours per day] from start date to ending date.
- when all dates with starting date is splited than should be splided all task with no [Start date], but there come some challanges: you have to split all hours, but they can't be bigger than [Effective hours]. For example: If algorithms finds that in 2018-11-29 team member has 3.5 hours load from previos tasks (with a start date) and he has jus 4 hours of efferctive hours, from this task it should bring to this date jus 0.5 hours and so on, till task [Remainig estimate] hours is spilted.
I hope I wrote in clear manner, if you need some example find attached Pbix and Excel files:
https://drive.google.com/open?id=1NNPA52xJ4cOUkN9xEYdLS6Ha4j-uPpZr
https://drive.google.com/open?id=1AzwRLQ7tEyYZ85Bosnovall4DwrA4mym
Is it posible to do it in Power BI? What DAX I should use to create dynamic table and algorithms?
Or maybe you know any report from JIRA that I can use?
Hi Anonymous,
What I can find for Question 1 is a calculated table. Please try it out.
Table = ADDCOLUMNS ( FILTER ( CROSSJOIN ( SELECTCOLUMNS ( JIRA, "Assignee", [Assignee], "Effective hours", [Effective hours], "IssueKey", [IssueKey], "Start date", [Start date], "DUEDATE", [DUEDATE], "Remaining estimate", [Remaining estimate], "date diff", [date diff], "IndexNew", [IndexNew], "SUMMARY", [SUMMARY] ), SELECTCOLUMNS ( 'Calendar', "Date", [Date] ) ), [Date] >= TODAY () && [Date] <= EDATE ( TODAY (), 2 ) ), "dd", [Solution] )
Regarding question 2, I would suggest you create a new thread in this forum.
Best Regards,
Dale
12 Replies
Hi Anonymous,
This is achievable. I have one question for now. Why is it 0.35 and 5.65? Why not 5 and 1? Please refer to the snapshot below.
The dynamic dates can be done like below.
BTW, don't post sensitive data.
Best Regards,
DaleThanks, v-jiascu-msft
0.35 and 5.65 is because task ASLU-79 has 0.35 hours time left for completeing the task, if we put 1 we will say that we have 0.75 extra time to complete the task.
And 5.65 is what is left from 0.35 hours for this day, if his effective working hours is 6 hours.
Is it clear enough?
Hi Anonymous,
Please download the solution from the attachment.
1. There isn't an order among the [Issue Key]. I added one. You can customize it yourself. (Check it out in the Query Editor).
2. Create a measure.
Solution = VAR accumulateRemaining = CALCULATE ( SUM ( JIRA[Remaining estimate] ), FILTER ( ALLEXCEPT ( JIRA, JIRA[Assignee] ), JIRA[IndexNew] <= MIN ( JIRA[IndexNew] ) ) ) VAR days = CALCULATE ( COUNT ( 'Calendar'[Date] ), FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] < MIN ( 'Calendar'[Date] ) && 'Calendar'[Date] >= TODAY () ) ) VAR accumulateToLast = accumulateRemaining - SUM ( JIRA[Remaining estimate] ) VAR leftBound = days * MIN ( JIRA[Effective hours] ) VAR rightBound = ( days + 1 ) * MIN ( JIRA[Effective hours] ) RETURN IF ( ISBLANK ( MIN ( JIRA[Start date] ) ), IF ( accumulateToLast <= leftBound && accumulateRemaining <= leftBound, 0, IF ( accumulateToLast <= leftBound && accumulateRemaining >= leftBound && accumulateRemaining <= rightBound, accumulateRemaining - leftBound, IF ( accumulateToLast >= leftBound && accumulateRemaining <= rightBound, accumulateRemaining - accumulateToLast, IF ( accumulateToLast <= leftBound && accumulateRemaining >= rightBound, MIN ( JIRA[Effective hours] ), IF ( accumulateToLast <= rightBound && accumulateRemaining >= rightBound, rightBound - accumulateToLast, 0 ) ) ) ) ), IF ( MIN ( JIRA[Start date] ) <= MIN ( 'Calendar'[Date] ) && MIN ( JIRA[DUEDATE] ) >= MIN ( 'Calendar'[Date] ), DIVIDE ( SUM ( JIRA[Remaining estimate] ), DATEDIFF ( MIN ( JIRA[Start date] ), MIN ( JIRA[DUEDATE] ), DAY ) ), 0 ) )
Please mark my answer as a solution if it works. It's really a big project.
Best Regards,
Dale