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

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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

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

        Dynamic-table-to-calculate-team-overload5

         

         

        Best Regards,
        Dale