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,

 

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?

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

    Hi Anonymous,

     

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

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

     

    Dynamic-table-to-calculate-team-overload4

     

    Best Regards,
    Dale

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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

       

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

        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