Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I have two columns, both with dates and times in the same format. I want to create a new column to calculate the days/times differences between the two columns. But when I try to change the data type to date/time, it gives me an error which makes sense because it's looking for an actualy date, whereas I am trying to calculate the difference between two dates.
Anybody know a good way to do this?
Thanks
Solved! Go to Solution.
You can also add a custom column in Query Editor.
=Duration.ToText([AlarmClearedUTC]-[AlarmSentUTC])
And then in DAX
Column = IF ( LEFT ( Table1[Duration], IFERROR ( SEARCH ( ".", Table1[Duration] ), 1 ) - 1 ) = "", 0, LEFT ( Table1[Duration], IFERROR ( SEARCH ( ".", Table1[Duration] ), 1 ) - 1 ) ) & " DAY " & RIGHT ( Table1[Duration], 8 )
Or Duration.ToRecord and expand?
Duration.ToRecord([AlarmClearedUTC]-[AlarmSentUTC])
Also can use a measure, i feel little faster loading time with this
TimeDiff = CONVERT(SELECTEDVALUE(Table[Date1],0)-SELECTEDVALUE(Table[Date2],0),DATETIME)
Thanks, this idea helped me on a similar issue i was batttling with.
@QuasemS wrote:
I have two columns, both with dates and times in the same format. I want to create a new column to calculate the days/times differences between the two columns. But when I try to change the data type to date/time, it gives me an error which makes sense because it's looking for an actualy date, whereas I am trying to calculate the difference between two dates.
Anybody know a good way to do this?
Thanks
What is the format of those two column? The data type conversion error indicates the format can't predicated correctly. Please post some sample of those column, if they're in the same format, it can be easy to re-format and convert to date.
Here's a snapshot of the two columns. I'm trying to find the difference between the second and the first one:
So the result wouldn't be a date, but in days/minutes/seconds format.
In your Query Editor window, add a custom column with a formula of:
[AlarmSentUTC] - [AlarmClearedUTC]
This will result in a Duration field that you can then use the blog article I posted earlier to format into the format you want.
I would get a raw number for that column. Do I format it using this:
Hours = INT([Duration in Seconds]/3600)
Minutes = INT(MOD([Duration in Seconds],3600)/60)
Seconds = MOD(MOD([Duration in Seconds],3600),60)
H = IF(LEN([Hours])=1,CONCATENATE("0",[Hours]),CONCATENATE("",[Hours]))
M = IF(LEN([Minutes])=1,CONCATENATE("0",[Minutes]),CONCATENATE("",[Minutes]))
S = IF(LEN([Seconds])=1,CONCATENATE("0",[Seconds]),CONCATENATE("",[Seconds]))
You can also add a custom column in Query Editor.
=Duration.ToText([AlarmClearedUTC]-[AlarmSentUTC])
And then in DAX
Column = IF ( LEFT ( Table1[Duration], IFERROR ( SEARCH ( ".", Table1[Duration] ), 1 ) - 1 ) = "", 0, LEFT ( Table1[Duration], IFERROR ( SEARCH ( ".", Table1[Duration] ), 1 ) - 1 ) ) & " DAY " & RIGHT ( Table1[Duration], 8 )
Or Duration.ToRecord and expand?
Duration.ToRecord([AlarmClearedUTC]-[AlarmSentUTC])
My data doesnt contain any days, when i use this power BI won't move the ifo to the table because of the zero days.
This is exactly what I'm looking for, but it doesn't work in Direct Query mode, do you have any alternatives for that?
Importing the data?
Does this help?
Or this?
http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.