working out time worked on a project

[Billy_1979]

Hi, 

Im new to power bi. i was wondering if you can help

i have a table for working  pattern for employees

i trying to work out how long they have worked on a project i have managed to work it out with the normal 9 to 5 employee but i am struggling to find a solution for the part time employees. 

so below is the data im using 

so i have used the following code which is ok for people working 9 to 5 monday to friday.

03- Hours spent =

VAR _Start = 'Task Duration'[firstDateMovedTo_Design WIP]

Var _End = 'Task Duration'[lastDateMovedOutOf_Design WIP]

Return SUMX(

            CALCULATETABLE(

                    'Calendar',

                    DATESBETWEEN('Calendar'[Date],_Start,_End),

                    'Calendar'[Workday] = 1

            ),

            MAX(MIN('Calendar'[End],_End) - MAX('Calendar'[Start],_Start),0) * 24

any help would be great thanks.

[Billy_1979]

Sorry for the late reply been working on other projects. so to give you a bit more info 

i trying to work out how long they have worked on a project i have managed to work it out with the normal 9 to 5 employee but i am struggling to find a solution for the part time employees/ employee who work 8:45 to 17:15.

I have managed to imported the data into my calendar table - i don't know if i have done it right below is the calendar.

How would i assign link this to the code below - 

03- Hours spent =

VAR _Start = 'Task Duration'[firstDateMovedTo_Design WIP]

Var _End = 'Task Duration'[lastDateMovedOutOf_Design WIP]

Return SUMX(

            CALCULATETABLE(

                    'Calendar',

                    DATESBETWEEN('Calendar'[Date],_Start,_End),

                    'Calendar'[Workday] = 1

            ),

            MAX(MIN('Calendar'[End],_End) - MAX('Calendar'[Start],_Start),0) * 24

So the question if the assignee (Shaun) is given  a ticket and it open for 3 days how would i work out how many hours he has worked on it ?

I hope this makes sense  and any help would be so grateful.

Thanks

[rubayatyasmin]

Hi, @Billy_1979 

Create a relationship between your employee table and your calendar table if you haven't done so already. This relationship should be based on the date.

to consider the working hours from the employee table. You will need to reference the working hours for each employee for each day within the task duration. This can be done by adding the employee's working hours into your SUMX function.

Also, you need to do some modification in your employee table and your calendar table should have a column where it identifies if it's workday.  

 Hours Spent =
VAR _Start = 'Task Duration'[firstDateMovedTo_Design WIP]
VAR _End = 'Task Duration'[lastDateMovedOutOf_Design WIP]
RETURN
SUMX(
CALCULATETABLE(
'Calendar',
DATESBETWEEN('Calendar'[Date], _Start, _End)
),
VAR CurrentDay = FORMAT('Calendar'[Date], "dddd")
VAR EmployeeWorkingHours = LOOKUPVALUE('Employee'[CurrentDay], 'Employee'[Name], RELATED('Calendar'[EmployeeName]))
RETURN MAX(MIN('Calendar'[End], _End) - MAX('Calendar'[Start], _Start), 0) * EmployeeWorkingHours
)