topic Re: calculate time difference between two timstamp columns in direct query mode in PowerBI in DAX Commands and Tips

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

yjk3140 — Mon, 29 Aug 2022 22:09:15 GMT

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!

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

amitchandak — Tue, 30 Aug 2022 06:39:00 GMT

yjk3140 , try if this measure can help

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

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

yjk3140 — Tue, 30 Aug 2022 11:54:09 GMT

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)

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

Anonymous — Thu, 01 Sep 2022 05:50:15 GMT

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.