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

[yjk3140]

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..

the result I want is 

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!

[v-binbinyu-msft · Accepted solution]

Hi @yjk3140 ,

Please try below steps.

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

2. add a new column with below dax formula

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

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]

@yjk3140 , try if this measure can help

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

[yjk3140]

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)