- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
working out time worked on a project
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.
any help would be great thanks.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 -
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
)
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
06-07-2024 08:08 AM | |||
08-19-2024 11:43 PM | |||
05-13-2024 12:45 AM | |||
08-22-2024 08:32 AM | |||
06-03-2024 08:27 AM |