Dynamic table to calculate team overload

[Anonymous]

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?

[v-jiascu-msft]

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,
Dale

[Anonymous]

Thanks, @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?

[v-jiascu-msft]

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

[Anonymous]

Hi @v-jiascu-msft,

Thanks a lot! Today I will try to make all calculation in our production.

One more question, when I try to calculate subtotoal's, it' dosent work, do you have any ideas why?

[v-jiascu-msft]

Hi @Anonymous,

Don't change anything, just add this measure as the final solution.

Solution 2 = sumx('JIRA', [Solution])

Best Regards,
Dale

[Anonymous]

Hi, @v-jiascu-msft

I moved calculation to production, everything works fine, exept those task wich have no start date, there are always - 0 hours.

I tried calculations in Excel everything works fine, but in Power BI they dosen't count, where could be the problem?

Link to excel file:

https://drive.google.com/open?id=1X8xDzloVxGz6mFoXsjmWFYJs7M4oUn1S

[Anonymous]

Sorry, @v-jiascu-msft my bad, I had foggoten Effective hours.

One more question, how can I calculate just working days?

I tried to filter Calendar query, but then report don't show today.

When I write additional filter in var day formula FILTER('Calendar'; 'Calendar'[WeekdayWeekend]="Weekday", it doesn't show any date.

[v-jiascu-msft]

Hi @Anonymous,

Please replace the [Solution] with this one.

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] )
                && MIN ( 'Calendar'[isWeekDay] ) = 1,
            DIVIDE (
                SUM ( JIRA[Remaining estimate] ),
                CALCULATE (
                    SUM ( 'Calendar'[isWeekDay] ),
                    FILTER (
                        ALL ( 'Calendar'[Date] ),
                        MIN ( JIRA[Start date] ) <= 'Calendar'[Date]
                            && MIN ( JIRA[DUEDATE] ) >= 'Calendar'[Date]
                            && MIN ( 'Calendar'[isWeekDay] ) = 1
                    )
                )
            ),
            0
        )
    )

Best Regards,
Dale

[Anonymous]

Thanks, @v-jiascu-msft it's working.

I hope it will be my last requests.

As I understand if I choose to see date hierarchy in the comulmns, the measure interpret month as a date and splited hours shows just 6. How can I see total splited hours for a month/ year? And is it possible to sum them?

And one more: is it possible that in thease cases, that there is no start date it will split days till Due Date, and if there any hours left he will put all in Due date (not looking at Effective hours). There won't be any hours splited after Due date

For Example if he has Remaining Estimate = 54.75 hours, ant task's Due date 2018-12-03, it will put all 54.75 to Due Date day.

And if we change Due date to - 2018-12-06, than it will be:

2018-12-03 = 6 hours

2018-12-04 = 6 hours

2018-12-05 = 6 hours

2018-12-06 = 36.75 hours

PBIX file:

https://drive.google.com/open?id=1g9VrtgdChSAPwVZKiQANz601uNUmXch8

[v-jiascu-msft · Accepted solution]

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

[Anonymous]

Thanks @v-jiascu-msft for the help.

All solution is here:

https://drive.google.com/open?id=1lfowi1yxKABf9IgLS3OX8FaRvHGb7cZI

[v-jiascu-msft]

Hi @Anonymous,

Can you share the .pbix file directly? Please mask the sensitive parts. 
1. Where is [Effective hours]?

2. Did you add the index?

Best Regards,
Dale