Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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 team member task and dates in this task. Something like that:

Overload.png

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?

 

1 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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

12 REPLIES 12
v-jiascu-msft
Microsoft Employee
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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?

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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?

Screenshot_3.png

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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?

no start date.png

 

Link to excel file:

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

 

Anonymous
Not applicable

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.

 

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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?

 

splited hours.png

 

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

due date.png

 

PBIX file:

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

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors