Forum Discussion

Billy_1979's avatar
Billy_1979
Frequent Visitor
2 years ago
Solved

help with different employee shift

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

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

10 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Billy_1979
      Frequent Visitor
      2 years ago

      Hi Anonymous  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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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?