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 wo...
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

     

Anonymous's avatar
Anonymous
Not applicable
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

 

Billy_1979's avatar
Billy_1979
Frequent Visitor
2 years ago

Thanks for your help with this query it worked perfectly.

 

Thanks again.