Trying to work out duration with date time columns and another with minutes

[metoo]

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 

[v-yiruan-msft · Accepted 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

[SamInogic]

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,

DATEDIFF(RoasterTable[Roaster Start Time],RoasterTable[Roaster End Time],HOUR)

If you want to calculate difference in Hours and minutes, then create a custom column with below expression,

Duration = DATEDIFF(RoasterTable[Roaster Start Time],RoasterTable[Roaster End Time],HOUR) & " Hour "  &MOD(DATEDIFF(RoasterTable[Roaster Start Time],RoasterTable[Roaster End Time],MINUTE),60) & " Minutes"

Please refer to the below screenshot ,

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/

[metoo]

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. 

[v-yiruan-msft · Accepted 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

[metoo]

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. 

[v-yiruan-msft · Accepted solution]

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

[metoo]

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.