Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 te...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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.

Dynamic-table-to-calculate-team-overload

The dynamic dates can be done like below.

Dynamic-table-to-calculate-team-overload2

 

BTW, don't post sensitive data.

 

Best Regards,
Dale

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

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

      Dynamic-table-to-calculate-team-overload3

       

      Please mark my answer as a solution if it works. It's really a big project.

       

      Best Regards,
      Dale

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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?