Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
QuasemS
Frequent Visitor

Calculating day/time difference

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

1 ACCEPTED SOLUTION

@QuasemS

 

You can also add a custom column in Query Editor.

=Duration.ToText([AlarmClearedUTC]-[AlarmSentUTC])

 

Capture.PNG

 

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 )

Capture.PNG

 

Or Duration.ToRecord and expand?

Duration.ToRecord([AlarmClearedUTC]-[AlarmSentUTC])

Capture.PNG

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

Also can use a measure, i feel little faster loading time with this 


TimeDiff = CONVERT(SELECTEDVALUE(Table[Date1],0)-SELECTEDVALUE(Table[Date2],0),DATETIME)

 
and then change the format to time hh:mm:ss
Cheers

Thanks, this idea helped me on a similar issue i was batttling with. 

Eric_Zhang
Microsoft Employee
Microsoft Employee


@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


@QuasemS

 

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:

snap1.PNG

 

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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

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]))

@QuasemS

 

You can also add a custom column in Query Editor.

=Duration.ToText([AlarmClearedUTC]-[AlarmSentUTC])

 

Capture.PNG

 

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 )

Capture.PNG

 

Or Duration.ToRecord and expand?

Duration.ToRecord([AlarmClearedUTC]-[AlarmSentUTC])

Capture.PNG

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.

 

Anonymous
Not applicable

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?

Greg_Deckler
Super User
Super User

Does this help?

 

http://community.powerbi.com/t5/DAX-Commands-and-Tips/Days-Between-Dates-with-Negatives/m-p/58303#U5...

 

Or this?

 

http://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.