Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am using Power BI to try and calculate Units Picked Per Hour and in order to do that I need to calculate Hours Worked
Each employee has multiple work orders and each work order has a first and a last item scanned. I am using a basic DATEDIFF function (MAX pick time minus MIN pick time) to calculate the time between the first scan and the last scan of each work order to calculate the time the employee was picking.
However, when I try to look at the TOTAL of those durations for each worker it gives me the difference between the employees first and last pick and the duration is incorrect.
Name | Work ID | Pick Time | Units Picked |
John Smith | 123 | 8:15 am | 2 |
John Smith | 123 | 8:16 am | 2 |
John Smith | 123 | 8:19 am | 2 |
John Smith | 123 | 8:22 am | 2 |
John Smith | 456 | 8:45 am | 5 |
John Smith | 456 | 8:50 am | 5 |
John Smith | 456 | 8:55 am | 5 |
So, I used a DATEDIFF to calculate the duration of work ID 123 and 456 and it returns 7 minutes and 10 minutes respectively. That's great. However, if I calculate the duration for John Smith it gives me the INCORRECT 40 minutes because its taking the MAX time minus the MIN time.
How can I create a function that finds the DATEDIFF for each work assignment and then SUMS them for each individual working?
Solved! Go to Solution.
@Jacob_Steagald try this measure to calculate the time between min and max
Pick Time Duration =
SUMX (
SUMMARIZE (
PickTime,
PickTime[Name],
PickTime[Work ID],
"@Min", MIN ( [Pick Time] ),
"@Max", MAX ( PickTime[Pick Time] )
),
DATEDIFF ( [@Min], [@Max], MINUTE )
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@Jacob_Steagald try this measure to calculate the time between min and max
Pick Time Duration =
SUMX (
SUMMARIZE (
PickTime,
PickTime[Name],
PickTime[Work ID],
"@Min", MIN ( [Pick Time] ),
"@Max", MAX ( PickTime[Pick Time] )
),
DATEDIFF ( [@Min], [@Max], MINUTE )
)
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Kudos! Thanks, my friend!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
101 | |
76 | |
64 | |
63 |
User | Count |
---|---|
141 | |
105 | |
101 | |
80 | |
67 |