Forum Discussion
Post Prodigyi need help with this one
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
Post ProdigyThe 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
Microsoft EmployeeHi 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
- RvdHeijden
first of all thanks for the explenation and your time.
I copied al of your formulas to my report however i think there is something not quite right.
if a task takes 8 workinghours and i have 4 days to do this task that means i have 4 days to do 8 hours work, it should return the value 2 for 'Working hours per day' right ?
If i have a task for 8 hours and i have 4 days time to do so, that means i need to work on that task for 2 hours work during 4 days.
It returns the value 0,25 now
- v-yulgu-msft
Hi RvdHeijden,
I noticed that you got 0.25 for 'Working hours per day' in table visual. Is the value correct in calculated table 'Working Table_2' I mentioned in original post? Can you show me your formula?
Regards,
Yuliana Gu- RvdHeijden
Yuliana, this is the formula im using
Working hours per day =
IF (
WEEKDAY ( 'Working Table_2'[Date]; 2 ) IN { 6; 7 };
BLANK ();
'Working Table_2'[Taakwerk] / 'Working Table_2'[Taakduur]
)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.
So it looks like we need to change the formula. So we either need a new calculated colum to 'convert' the column 'Taakduur' which is in hours to workingdays or change the formula.
Here is another example because my task takes 8 hours and i get 1 day but here it shows 8 hours so basically the formula should return the value 8