Forum Discussion

Post Prodigy

9 years ago

Solved

i 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. Futherm...

Need Help

v-yulgu-msft
9 years ago
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

fhill

Resident Rockstar

9 years ago

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

Post Prodigy

9 years ago

fhill

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-msft
Microsoft Employee
9 years ago
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
- RvdHeijden
  Post Prodigy
  9 years ago
  v-yulgu-msft
  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
    Microsoft Employee
    9 years ago
    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