Distribute hours in day and know expected delivery date (PROJECTS CONTROL)

[jrscaletta]

Hi all!

I'm learning PowerBi and I'm trying to make a new simple dashboard that allow me to know when is the expected delivery date of my projects based on workload and priority..

For that I prepared an Excel Worksheet like this:

In the other hand, now in PowerBI, I've prepared a datetable to know if a day is a labor day or not:

Then, knowing that developer can Work 6 hours per day, I need to know in what date we are going to close the projects. It can be in Gantt chart o bar chart. I need something like this:

The codes in the image above are the priority code of project and the blocks simulates labor weeks.

For do that, what tables structure do I need to have? I don't know how to relationate "DATETABLE" with "PROJECTS TABLE". I need to associate the projects to the date and reduce the hours planned each day by 6. The calculation should always start on the next labor day.

It's possible too that developer work on two diferrent projects or more on the same day. It depends on the hours structure. In example, if we have a PROJECT(A) - 3 hours and PROJECT(B) - 3 hours too, with top 2 priorities, he will work on both projects and close both on the first next labor day.

Any help will help me guide my PowerBi design.

Thanks in advance.

[Greg_Deckler]

I've built similar types of things (burndown charts) in Power BI. Usually you have a project start date, but that's not really necessary I guess. Also, would be of tremendous help to have this data posted as text. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

So, couple thoughts on this. You could always get a start date by doing something like this measure

Start Date = 
VAR __today = TODAY()
VAR __table = FILTER('Calendar',[Date]>=__today && [LaborDay] = 1)
RETURN
MINX(__table,[Date])

This should always return the next labor day based upon TODAY's date.

Once you have that, you can create a burn down measure like:

Burndown =
VAR __InitialHours = MAX('Projects'[Hours])
VAR __date = MAX('Calendar'[Date])
VAR __table = FILTER(ALL('Calendar'),[Date]>=[Start Date] && [Date] <= __date)
VAR __burned = SUMX(__table,[HoursByDayDeveloper])
RETURN
__InitialHours - __burned

Something along those lines perhaps

Also, check out this Text Gantt Chart Quick Measure, might help as well.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Text-Gantt-Chart/m-p/253466

[jrscaletta]

Thanks Greg. Your reply helps to me to understand the operation.

I tried to use your tips to publish my question, but I don't know how to obtain data in plain text from Power BI.

Then, I added the 2 structures that you said as a "New Column" for see the results, and that's it:

(I tried to use a Measure too, but the result is the same)

Priority   Project	       Hours            Role
10	Project 1	          45            Developer
20	Project 2	          12	        Developer
30	Project 3	           7	        Developer
40	Project 4	          14	        Developer
50	Project 5	           6	        Developer
60	Project 6	          30	        Developer
70	Project 7	          10	        Developer
80	Project 8	          30	        Developer
90	Project 9	          50	        Developer
100	Project 10	          21            Developer
110	Project 11	           6            Developer

If you see the image above, you will see that the "Start Date" is the same for all my projects. That's not correct.

Start Date = 
VAR __today = TODAY()
VAR __table = FILTER('Calendar';[Date]>=__today && [LaborDay] = 1)
RETURN
MINX(__table;[Date])

Power BI must consider that we have a queue of projects and we must finish them in priority order. Then, for the Project 1, we need 45 hours. The developer works 6 hours per day, then: 45 / 6 = 7,5 labor days to finish Project 1. To construct the Gantt, I need to know the Finish Date, in that case:

PROJECT 1 - Start Date: 28/01/19 - Initial Hours: 45

28/01/19 - Working Time: 6 - Hours left: 39

29/01/19 - Working Time: 6 - Hours left: 33

30/01/19 - Working Time: 6 - Hours left: 27

31/01/19 - Working Time: 6 - Hours left: 21

01/02/19 - Working Time: 6 - Hours left: 15

02/02/19 - NON LABOR DAY

03/02/19 - NON LABOR DAY

04/02/19 - Working Time: 6 - Hours left: 9

05/02/19 - Working Time: 6 - Hours left: 3

06/02/19 - Working Time IN PROJECT 1: 3 - Hours left: 0 ---- FINISH DATE PROJECT1

PROJECT 2 - START DATE 06/02/19 - Initial Hours: 12

06/02/19 - Working Time: 3 - Hours left: 9

07/02/19 - Working Time: 6 - Hours left: 3

08/02/19 - Working Time IN PROJECT 2: 3 - Hours left: 0 ---- FINISH DATE PROJECT2

..

Then, how could I obtain the START DATE and FINISH DATE for every project? With that info, I will prepare the Gantt that you linked in your first reply.

Thank you very much for your help.

[jrscaletta]

In fact, I only need to know the Start Date and the Finish Date for every project, keeping in mind the priority of each project and working days only.

Thanks.

[v-juanli-msft]

Hi @jrscaletta

It's possible too that developer work on two diferrent projects or more on the same day. It depends on the hours structure. In example, if we have a PROJECT(A) - 3 hours and PROJECT(B) - 3 hours too, with top 2 priorities, he will work on both projects and close both on the first next labor day.

---

If you have many developers working on these projects, i can't detemine which is the start date for each project.

If you can accept the following situtation:

after project1(priority1) completed, then project2(priority2) starts.

This problem may become less difficult.

Best regards

Maggie

[jrscaletta]

Hi Maggie.

Thanks for your reply.

I accept this situation: after project1(priority1) completed, then project2(priority2) starts.

How can I proceed?

Thanks