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.
Hi,
I'm not sure how to do this in Power BI Desktop. Any help you can provide me is greatly appreciated.
I have a table that contains two managers (Manager 1 and Manager 2) with different work shift and break time for each day.
Then another table which contains the finished date and time of a product.
What I need is a similar table like below wherein:
1. Date - is the date the when the product was finished.
2. Manager - Manager who is assigned to that shift when the product was finished.
3. # of Hour Finished - Computed by getting the earliest finished time - latest finished time, and removing the time spent during break time. Value should be in hours.
4. # of Minutes Finished - Computed by getting the earliest finished time - latest finished time, and removing the time spent during break time. Value should be in minutes.
5. # of Seconds Finished - Computed by getting the earliest finished time - latest finished time, and removing the time spent during break time. Value should be in seconds.
If anything is unclear please let me know.
Best regards,
Mark V.
Solved! Go to Solution.
@markefrody , sorry, I've missed some details.
hh =
VAR timeIn = MIN ( 'Table 1'[Start Time] )
VAR timeOut = MAX ( 'Table 1'[Clock Out] )
VAR timeRange =
FILTER (
VALUES ( 'Table 2'[Date and Time Finished] ),
'Table 2'[Date and Time Finished] >= timeIn
&& 'Table 2'[Date and Time Finished] <= timeOut
)
VAR break = DATEDIFF( MIN ( 'Table 1'[Break1 Start] ), MAX ( 'Table 1'[Break1 End] ), SECOND)
VAR ss = DATEDIFF ( MINX ( timeRange, 'Table 2'[Date and Time Finished] ), MAXX ( timeRange, 'Table 2'[Date and Time Finished] ), SECOND ) - break
VAR mm = ss / 60
VAR hh = mm / 60
RETURN
hh
Just use what you need in the return (ss / mm / hh) and change the measure name accordingly.
Regards
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @ERD,
Thank you for your solution. When you say that both tables are connected via seperate Date table by Date column, does it mean I need to setup a relationship between Table 1 and Table 2?
you need to set up relations between
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @ERD,
I tried to setup the relationship of the date table to tables 1 and 2 but it seems I am not getting the right hours. I have placed the Power BI file with the tables for reference in the link below:
https://www.dropbox.com/s/2a0k6javqjjw7wx/Production%20Hours%20per%20Manager.pbix?dl=0
@markefrody , please, use Date column from the Date table in your visual.
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @ERD,
Have now applied the Date column in the Data table but I am getting 0 hours in most of the days.
As per checking, there is production hours (Table 2) for these days.
@markefrody , sorry, I've missed some details.
hh =
VAR timeIn = MIN ( 'Table 1'[Start Time] )
VAR timeOut = MAX ( 'Table 1'[Clock Out] )
VAR timeRange =
FILTER (
VALUES ( 'Table 2'[Date and Time Finished] ),
'Table 2'[Date and Time Finished] >= timeIn
&& 'Table 2'[Date and Time Finished] <= timeOut
)
VAR break = DATEDIFF( MIN ( 'Table 1'[Break1 Start] ), MAX ( 'Table 1'[Break1 End] ), SECOND)
VAR ss = DATEDIFF ( MINX ( timeRange, 'Table 2'[Date and Time Finished] ), MAXX ( timeRange, 'Table 2'[Date and Time Finished] ), SECOND ) - break
VAR mm = ss / 60
VAR hh = mm / 60
RETURN
hh
Just use what you need in the return (ss / mm / hh) and change the measure name accordingly.
Regards
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Hi @markefrody ,
Next time, please, provide sample data as text, use the table tool in the editing bar.
You can use the measure below to get your hours:
hh =
VAR timeIn = HOUR ( MIN ( 'T1'[TimeIn] ) )
VAR timeOut = HOUR ( MAX ( 'T1'[TimeOut] ) )
VAR timeRange =
FILTER (
VALUES ( 'T2'[DateTime] ),
HOUR ( 'T2'[DateTime] ) >= timeIn
&& HOUR ( 'T2'[DateTime] ) <= timeOut
)
VAR break = HOUR ( MIN ( 'T1'[BreakStart] ) - MAX ( 'T1'[BreakEnd] ) )
VAR hh = HOUR ( MINX ( timeRange, [DateTime] ) - MAXX ( timeRange, [DateTime] ) ) - break
RETURN
hh
Please, take into account that both tables are connected via a separate Date table by Date column.
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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 |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |