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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
yjk3140
Helper I
Helper I

calculate time difference between two timstamp columns in direct query mode in PowerBI

I need to calculate the time difference between two timestamp columns in my table and create a new column with the calculated values but as I'm doing this in the direct query mode, I have some limitations for it. I tried datediff, duration.totalseconds etc but nothing really worked..

yjk3140_1-1661809656879.png

the result I want is 

yjk3140_2-1661810596730.png

I got this result using the sql below

 

select (unix_timestamp(timestamp1)-unix_timestamp(timestamp2))/3600 as hourly_diff from azure_anomaly_detection_pilot.ops_log

 

Could anyone help me on this?

 

Thank you!

 

 

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @yjk3140 ,

Please try below steps.

1.in power query pane, change the data type to "Date/Time/Timezone"

vbinbinyumsft_0-1662011049248.png

vbinbinyumsft_1-1662011104172.png

 

2. add a new column with below dax formula

Hourly Diff =
DATEDIFF ( 'Table'[Time stamp2], 'Table'[Time stamp 1], SECOND ) / 3600

vbinbinyumsft_2-1662011221010.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi @yjk3140 ,

Please try below steps.

1.in power query pane, change the data type to "Date/Time/Timezone"

vbinbinyumsft_0-1662011049248.png

vbinbinyumsft_1-1662011104172.png

 

2. add a new column with below dax formula

Hourly Diff =
DATEDIFF ( 'Table'[Time stamp2], 'Table'[Time stamp 1], SECOND ) / 3600

vbinbinyumsft_2-1662011221010.png

Please refer the attached .pbix file.

 

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@yjk3140 , try if this measure can help

 

Sumx(Table, Table[Timestamp1], Table[timestamp2], second)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi @amitchandak , I tried this but I'm getting an syntax error for this solution.

I think the correct syntax for sumx is sumx(table, expression)

Helpful resources

Announcements
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! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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