Dynamic table to calculate team overload

Thanks v-jiascu-msft for the help.

All solution is here:

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

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

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