topic Re: Trying to work out duration with date time columns and another with minutes in DAX Commands and Tips

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

Anonymous — Wed, 06 Dec 2023 00:42:01 GMT

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

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

SamInogic — Wed, 06 Dec 2023 05:22:59 GMT

Hi Anonymous ,

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/

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

Anonymous — Fri, 08 Dec 2023 02:32:46 GMT

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.

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

Anonymous — Tue, 12 Dec 2023 10:01:53 GMT

Hi Anonymous ,

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

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

Anonymous — Wed, 13 Dec 2023 00:46:10 GMT

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.

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

Anonymous — Wed, 13 Dec 2023 04:40:09 GMT

Hi Anonymous ,

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

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

Anonymous — Wed, 13 Dec 2023 06:09:31 GMT

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.