i need help with this one

[RvdHeijden]

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

[fhill]

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]

@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]

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]

@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]

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]

@v-yulgu-msft

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

[v-yulgu-msft · Accepted solution]

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