Forum Discussion
Helper Icalculate 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..
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!
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 ) / 3600Please 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.
3 Replies
- amitchandak
Super User
yjk3140Hi 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)
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 ) / 3600Please 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.
