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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Magnus-CPH-DK
Helper I
Helper I

Difference between two datetime values in format dd:hh:mm:ss

Hi there

 

I have two datetime columns:

 

MagnusCPHDK_0-1677069027889.png

 

Power Query recognizes the column data types as datetimes. However, I am not sure if the formatting (probably regional) causes problems. When I add this custom column in Powre Query I get numbers out that looks about right although they are in a horrible format:

 

MagnusCPHDK_1-1677069238933.png

 

Result:

 

MagnusCPHDK_2-1677069361856.png

 

 

But when I try to convert the column type to datetime to hopefully get a format that's easier on the eye, I get the following error:

 

 

MagnusCPHDK_3-1677069446587.png

 

 

Changing the column type to duration makes no difference to the values, and the type time returns the same error as above.

 

I also tried to solve the problem in DAX by concatenating 4 types of DATEDIFF (days, hours, minutes and seconds - why the heck don't they have an easier DAX formula for that instead of having to use 4 DATEDIFF expressions?!). This creates other issues, such as returning the value 1 day if the datetime value shifts from just before midnight to after midnight (instead of returning the value 1 only after 24 hours of duration).

 

I want the following result:

 

Time difference bwteen lastRefresh_startTime 15-02-2023 23:09:43 and lastRefresh_endTime 16-02-2023 02:16:24 =

 

0 days, 3 hours, 6 minutes, 41 seconds 

 

or even better:

 

00:03:06:41

 

 

Thanks in advance

 

 

 

 

 

1 ACCEPTED SOLUTION
serpiva64
Super User
Super User

Hi,

i think your problem is Milliseconds which are in your datetime.

You have to extract Text before delimiter in each column using . as delimiter

serpiva64_0-1677073420928.png

(and if you prefer in the same step change type text to type datetime)

 = Table.TransformColumns(#"Changed Type", {{"Column1", each Text.BeforeDelimiter(Text.From(_, "it-IT"), "."), type datetime}})

then you use your formula

and it will be all right

When you change finally the type you need to use Duration and not Datetime.

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

 

p.s.: pay attention to the fact that you will not be able to use duration in your report (when you load your duration is converted to decimal). Refer to this in order to handle this problem

https://community.powerbi.com/t5/Desktop/Duration-format/m-p/447543

View solution in original post

1 REPLY 1
serpiva64
Super User
Super User

Hi,

i think your problem is Milliseconds which are in your datetime.

You have to extract Text before delimiter in each column using . as delimiter

serpiva64_0-1677073420928.png

(and if you prefer in the same step change type text to type datetime)

 = Table.TransformColumns(#"Changed Type", {{"Column1", each Text.BeforeDelimiter(Text.From(_, "it-IT"), "."), type datetime}})

then you use your formula

and it will be all right

When you change finally the type you need to use Duration and not Datetime.

If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

 

p.s.: pay attention to the fact that you will not be able to use duration in your report (when you load your duration is converted to decimal). Refer to this in order to handle this problem

https://community.powerbi.com/t5/Desktop/Duration-format/m-p/447543

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors