Forum Discussion
Post Prodigyi need help with this one
Goodday,
ill try to explain my question as clear as i possibly can.
i have a table and every row is a task with an 'begindate' and an 'enddate' so i know how long that task may take.
Futhermore i have 2 additional colums 'TaskDuration' and 'TaskWork' for example TaskWork is 2 (hours) and TasDuration is 5 workingdays that means that the task takes 10 hours but the person has 5 days in which he has to do those 10 hours of work
I hope this part is clear so far.
What i want is (probably) a line graph or a bar chart so that i can see how much work that person has over time
When i do it given this data my visual will place the total amount of TaskWork on the first day of the Task (Begindate) so that gives a lot of peaks in my graph.
What i want is that if a task takes 10 hours in the next 5 days that means it takes 2 hours a day so my graph should show in that periode of 5 workingdays 5 bars each for 2 hours
My thought so far is that i probably need a 'Calendar' table in my report so that i can calculate per day the amount of work per date (rows) i also need al my resources (people) to be in the colums so i get a giant matrix with dates in the rows and people in the colums and then a formula that calculates per date, per person the amount of work they have that day.
It is possible to have more then 1 task per day so that should be taken in consideration.
i hope ive been clear enough if not please let me know ill try to answer your questions as good as i can.
thanks in advance
Hi RvdHeijden,
Im not sure but i think it has something to do with the fact that my initial question was that the duration was given in Workingdays.
but when i see the data in my table it looks like MS Project uses Hours in the database, where in PWA it displays Days.
In that case, you only need to make a little adjustment to the formula:
Working hours per day =
IF (
WEEKDAY ( 'Working Table_2'[Date]; 2 ) IN { 6; 7 };
BLANK ();
'Working Table_2'[Taakwerk] / ('Working Table_2'[Taakduur]/8)
)Regards,
Yuliana Gu
7 Replies
- fhill
Resident Rockstar
Does this table have the assigned employee also? If you can give us some sample data, it will go a long way in helping you build a visual...
"i have a table and every row is a task with an 'begindate' and an 'enddate' so i know how long that task may take.Futhermore i have 2 additional colums 'TaskDuration' and 'TaskWork'"
- RvdHeijden
The employees aren't in the same table but in another table, but there is a relationship between Task.iD in the 'Task' tabel and the Task.iD' in 'Assigned' so that is possible
This is a example of the data, i hope this will make it clear
Task Begindate Enddate Person Work in hours Duration in workingdays
Taskid 1 25-08-17 31-08-17 A 10 5
Taskid 2 28-08-17 29-08-17 B 2 2
Taskid 3 28-08-17 28-08-17 A 1 1
Taskid 4 31-08-17 01-09-17 B 4 2
EDIT: the table below is the way i need the information
Date IsWorkday Person A Person B
25-08-17 1 2
26-08-17 0
27-08-17 0
28-08-17 1 3 1
29-08-17 1 2 1
30-08-17 1 2
31-08-17 1 2 2
01-09-17 1 2
v-yulgu-msftMicrosoft Employee
Hi RvdHeijden,
First, you need a calendar table like this:
Calecdar Table = CALENDAR ( MIN ( 'Working Table'[Begindate] ), MAX ( 'Working Table'[Enddate] ) )
Then, create a new calculated table via cross join calendar table and working table.
Working Table_2 = FILTER ( CROSSJOIN ( 'Calecdar Table', 'Working Table' ), 'Calecdar Table'[Date] >= 'Working Table'[Begindate] && 'Calecdar Table'[Date] <= 'Working Table'[Enddate] )
Also, add below calculated column in 'Working Table_2'.
Is weekday = WEEKDAY('Working Table_2'[Date],2)
Working hours per day = IF ( WEEKDAY ( 'Working Table_2'[Date], 2 ) IN { 6, 7 }, BLANK (), 'Working Table_2'[Work in hours] / 'Working Table_2'[Duration in workingdays] )Use a matrix to display data.
Best regards,
Yuliana Gu
