Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |