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 all
I'm having trouble finding a solution to this in the forums. I have two columns for datetime values for vessel arrivals and departures, DateTime Berthed and DateTime Departed Berth.
I need to find the duration between these two dates in hours and minutes, in a calculated column numerically so they can be summed by different categories (ie vessel type, month, etc) for berth utilisation.
Here is a snippet of the data:
When I use DATEDIFF I can only get either hours or minutes, ie:
Hours = DATEDIFF('Berth Utilisation'[DateTime Berthed], 'Berth Utilisation'[DateTime Departed Berth], HOUR)
I don't want to have two columns that I have to concatenate as it then becomes a text value and I need to be able to sum up the column.
Can anyone assist please?
Hi @Anonymous
If you just subtract one column from the other you'll have the difference in days. If you multiply that by 24*60 you'll have it in minutes. You can then operate normally with it and convert it to hours and minutes or HH:MM format later on when required.
TimeDiffInMins = (Table1[Departed Berth] - Table1[Berthed]) * 24*60
Hi there, thanks for your reply. When I use the following formula as suggested:
The returned value for each line looks like this:
"System.FormatException: The string '-169521-12-11T05:15:00' is not a valid AllXsd value.
at System.Xml.Schema.XsdDateTime..ctor(String text, XsdDateTimeFlags kinds)
at System.Xml.XmlConvert.ToDateTime(String s, XmlDateTimeSerializationMode dateTimeOption)
at Microsoft.AnalysisServices.AdomdClient.FormattersHelpers.ConvertToDateTime(String s)
at Microsoft.AnalysisServices.AdomdClient.FormattersHelpers.ConvertFromXml(String xmlValue, Type targetType, Boolean convertLocalTime)"
@Anonymous
That's weird. Are you using Power BI Desktop or something else? Are the columns of date type?
Anyway if the DATEDIFF was working, you could do the same with:
TimeDiffInMins =
DATEDIFF('Berth Utilisation'[DateTime Berthed], 'Berth Utilisation'[DateTime Departed Berth], HOUR)*60 + DATEDIFF('Berth Utilisation'[DateTime Berthed], 'Berth Utilisation'[DateTime Departed Berth], MINUTE)
@AlB Thanks for that. Yes, I'm using PBi Desktop, and the column formats are Date/Time format.
The second formula you provided doesn't seem to give the correct value; for example I have DateTime Berthed of 15/01/2019 11:35:00 AM and DateTime Departed of 15/01/2019 6:45:00 PM and this formula gives me a value of 850
@Anonymous
OK, try just
TimeDiffInMins = DATEDIFF('Berth Utilisation'[DateTime Berthed], 'Berth Utilisation'[DateTime Departed Berth], MINUTE)
@Anonymous
By the way, you need the parentheses as I wrote them in the previous version. With them you won't get the error
TimeDiffInMins = (Table1[Departed Berth] - Table1[Berthed]) * 24*60
Thanks, that just appears to give me the minutes.
Is there no way to get the hours and minutes in some kind of duration format that is numeric? Ie 7:10 instead of just having to accept 7 (if I use hours) or 430 for minutes?
@Anonymous
TimeDiff = 'Berth Utilisation'[DateTime Departed Berth]-'Berth Utilisation'[DateTime Berthed]
and choose the format HH:mm
I'm not sure how that will work out with addition and subtraction later. You'll probably run into problems as soon as it gets over 24h, as that will be shown as a day so I wouldn't recommend that
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |