help with different employee shift

[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 

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

[v-jingzhan-msft]

Hi @Billy_1979 

My idea is that you need to add a column to Employee table to identify whether an employee is a full-time employee or a part-time employee. Add part-time start and end columns to Calendar table. Then create a measure to calculate the hours spent for part-time employees separately. When calculating hours spent for an employee, use an IF statement like if the employee is full-time, then use [Measure for full-time] else use [Measure for part-time].

The problem is that every part-time employee only works on several workdays in a week, so the current 'Calendar'[Workday] = 1 doesn't apply to them. You can add an additional table to have workdays for only part-time employees like below. Connect this table to the original Employee table on Employee column. 

Steps: duplicate the employee table, filter it to have only part-time employees, unpivot "Monday" to "Friday" 5 columns. 

Then try a measure like 

part-time Hours spent =
VAR _Start = 'Task Duration'[firstDateMovedTo_Design WIP]
Var _End = 'Task Duration'[lastDateMovedOutOf_Design WIP]
VAR _workdays = VALUES('Part-time employee workdays'[Weekday])
Return SUMX(
            CALCULATETABLE(
                    'Calendar',
                    DATESBETWEEN('Calendar'[Date],_Start,_End),
                    'Calendar'[Workday] = 1,
                    'Calendar'[WeekdayName] IN _workdays
            ),
            MAX(MIN('Calendar'[part End],_End) - MAX('Calendar'[part Start],_Start),0) * 24

Above is an idea without testing. You may need to modify the measure according to your table structures and model. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Billy_1979]

Hi @v-jingzhan-msft  thanks for your suggestion.

I have altered the calendar table 

So the question is that in the table below  

how would i use the code below to match assignee to the calendar and only extra the hours in between the dates move to and move out?

part-time Hours spent =
VAR _Start = 'Task Duration'[firstDateMovedTo_Design WIP]
Var _End = 'Task Duration'[lastDateMovedOutOf_Design WIP]
VAR _workdays = VALUES('Part-time employee workdays'[Weekday])
Return SUMX(
            CALCULATETABLE(
                    'Calendar',
                    DATESBETWEEN('Calendar'[Date],_Start,_End),
                    'Calendar'[Workday] = 1,
                    'Calendar'[WeekdayName] IN _workdays
            ),
            MAX(MIN('Calendar'[part End],_End) - MAX('Calendar'[part Start],_Start),0) * 24

Or have i made it more difficult for myself with the change to the calendar?

Thanks for the earlier reply.

[v-jingzhan-msft]

Hi @Billy_1979 

In the new calendar, there are several Start Times and End Times. Do these Start Times and End Times represent the regular work time range for each employee on each date? For some people the Start Time and End Time is blank, does this mean that they don't need to work on that date? If so, can we change the "Workday" value to 0 on those rows?

In the second table, how do you hope to treat the rows that have null values? Especially when the MoveTo has a value while the MovedOutOf is null? 

[Billy_1979]

Hi Thanks for the reply my answers to your questions are in bold.

In the new calendar, there are several Start Times and End Times. Do these Start Times and End Times represent the regular work time range for each employee on each date?   YES For some people the Start Time and End Time is blank, does this mean that they don't need to work on that date? If so, can we change the "Workday" value to 0 on those rows?   YES so i'll put in 0 in the blank field.

 In the second table, how do you hope to treat the rows that have null values? Especially when the MoveTo has a value while the MovedOutOf is null? So with the movedoutof is null then we don't capture that time until the moveoutof date is populated.

[v-jingzhan-msft]

Hi @Billy_1979 Thank you for these answers.

As the new calendar table has all date rows for each employee (including full-time and part-time employees), and after modifying the calendar table into that the workday column has correct value 1 for each employee's workday and 0 for no-workday, your original measure should work for both full-time and part-time employees. The possible change may be for the variables _Start and _End. 

new Hours spent =
VAR _Start = CALCULATE(MIN('Task Duration'[firstDateMovedTo_Design WIP]),'Task Duration'[firstDateMovedTo_Design WIP]>BLANK())
Var _End = MAX('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

Best Regards,
Jing

[Billy_1979]

Hi Thanks for your reply, i tried the code and it looks like its bringing back everyone who as worked that day rather than the particalur employee assigned to the project. please find below the screen shots.

Calendar table

Also its bringing back results when there isn't an lastdatemoveoutof date.

so i think the issue is how do i assign the employee to the task date ?

I hope that makes sense.

[v-jingzhan-msft]

Sorry I thought you were creating a measure. I didn't realize this was a calculated column. How about try your original formula with the workday1 column?

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'[Workday1] = 1
            ),
            MAX(MIN('Calendar'[End],_End) - MAX('Calendar'[Start],_Start),0) * 24

[Billy_1979]

Hi thanks for the reply- 

i tried that yesterday and as i said its looks like its bringing back everyone who as worked that day rather than the particalur employee assigned - 

how would i assign a employee for each project.

Thanks.

[v-jingzhan-msft · Accepted solution]

Hi @Billy_1979 

Do you link the 'Task Duration' table to 'Calendar' table on Employee column? From the current data, it will be a many-to-many relationship: 'Task Duration'[assignee]  -- 'Calendar'[Employee]

If building above relationship doesn't make the formula work correctly, remove this relationship and try this formula instead:

03- Hours spent =
VAR _Start = 'Task Duration'[firstDateMovedTo_Design WIP]
Var _End = 'Task Duration'[lastDateMovedOutOf_Design WIP]
Var _assignee = 'Task Duration'[assignee]
Return SUMX(
            CALCULATETABLE(
                    'Calendar',
                    DATESBETWEEN('Calendar'[Date],_Start,_End),
                    'Calendar'[Workday1] = 1,
                    'Calendar'[Employee] = _assignee
            ),
            MAX(MIN('Calendar'[End],_End) - MAX('Calendar'[Start],_Start),0) * 24

[Billy_1979]

Thanks for your help with this query it worked perfectly.

Thanks again.