Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi there all,
I'm trying to work out a total hours and minutes duration between a start time, end time and then add a to the duration. How can I do this in power bi desktop? (I only need the end calculation of the total time in hours and minutes).
Thanks
Solved! Go to Solution.
Hi @metoo ,
You can create a calculated column as below to get it:
Column =
VAR _Seconds =
(
DATEDIFF ( 'Table'[RosterStartTime], 'Table'[RosterEndTime], MINUTE ) + 'Table'[TravelTimeFinal]
) * 60
VAR _Minutes =
INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds =
MOD ( _Seconds, 60 )
VAR _Hours =
INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes =
MOD ( _Minutes, 60 )
RETURN
FORMAT ( _Hours, "00" ) & ":"
& FORMAT ( _RemainingMinutes, "00" ) & ":"
& FORMAT ( _RemainingSeconds, "00" )
Best Regards
Hi @metoo ,
According to your screenshot, it seems that you are trying to create a custom column in Power Query Editor. Please create a calculated column by DAX instead of creating a custom column.
Solved: Power Query Custom Column vs. Calculated Column: P... - Microsoft Fabric Community
Power BI: Custom Column Vs Calculated Column | Power BI
Best Regards
Hi @metoo ,
In Power BI, we have DateDiff function to calculate the difference between dates in various intervals such as Hour, Minutes, etc.
So, you can use below expression to calculate difference in Hours,
Thanks!
Inogic Professional Service Division
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Thanks SamInogic for that information. Sorry I wasnt clear in my questions. What I was really saying was I would like the final column to contain both the duration and the added travel time for the shift as well. The format would be hours and minutes. So in the eg above, it would be 1.30 minutes duration + the travel time, so all up 1.45 minutes.
Cheers
Metoo
ps: if you need to know using power bi desktop.
Hi @metoo ,
You can create a calculated column as below to get it:
Column =
VAR _Seconds =
(
DATEDIFF ( 'Table'[RosterStartTime], 'Table'[RosterEndTime], MINUTE ) + 'Table'[TravelTimeFinal]
) * 60
VAR _Minutes =
INT ( DIVIDE ( _Seconds, 60 ) )
VAR _RemainingSeconds =
MOD ( _Seconds, 60 )
VAR _Hours =
INT ( DIVIDE ( _Minutes, 60 ) )
VAR _RemainingMinutes =
MOD ( _Minutes, 60 )
RETURN
FORMAT ( _Hours, "00" ) & ":"
& FORMAT ( _RemainingMinutes, "00" ) & ":"
& FORMAT ( _RemainingSeconds, "00" )
Best Regards
Thansk for the repsonse. I'm getting a eof error when trying to add. Do you think this could be related to the data types? I've tried duplicating the start and end columns and confirming date/time. That didnt help. The travel time column is formated as decimal number. There will be a lot of zeros in this data.
Hi @metoo ,
According to your screenshot, it seems that you are trying to create a custom column in Power Query Editor. Please create a calculated column by DAX instead of creating a custom column.
Solved: Power Query Custom Column vs. Calculated Column: P... - Microsoft Fabric Community
Power BI: Custom Column Vs Calculated Column | Power BI
Best Regards
Thank you very much, calculated beautifully.
Sorry one final question, how can this number be summed? (using a slicer to work out the fortnight). Matching the total hour+ travel time against another column called minimum hours.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |