Calculating difference between two timestamps using DAX

[yjk3140]

Hi,

I need to create a DAX measure which calculates difference in hours between two timestamp columns in a table.

the date format for those columns is yyyy-mm-ddThh:mm:ss:00.000+0000 ex) 2022-05-03T16:35:00.000+0000.

I tried using DATEDIFF(column1,column2,hour) but it didn't work but threw an error.

I was able to get the result I want through the sql query below in databricks using unix_timestamp function

select (unix_timestamp(coumn1)-unix_timestamp(column2))/3600 as hourly_diff from table1.

Could anyone help with this query?

Thanks for any kind of help in advance!

[Daryl-Lynch-Bzy]

Hi @yjk3140 - I spotted this community posing that suggested that Direct Query was not available for datetimestamps with milliseconds.  Solved: Convert UNIX timestamp to DateTime using Direct Qu... - Microsoft Power BI Community.  Is there anyway to change the Databricks table to include the Unix TimeStamp as columns without the need for the addition of the unix_timestamp() function in Power BI query?

[Daryl-Lynch-Bzy]

Hi @yjk3140  - Should you please share some same data, the DAX measure formula, the error message that the measure threw.  If possible could you create a PBIX file that replicates the error with anonymous data.

[yjk3140]

Hi @Daryl-Lynch-Bzy thanks for the response.

This is a screenshot of data for these two timestamp columns

and the DAX query I wrote is 

duration = DATEDIFF(table1[timestamp1],table1[timestamp2],HOUR)

I tried to see the created column through the table visual but I'm getting an error saying "we couldn't fold the expression to the data source. Please try a simple expression. Getting the same error when I'm trying to use it in a filter for other visuals.

When I run the SQL query 

select (unix_timestamp(timestamp1)-unix_timestamp(timstamp2))/3600 as hourly_diff from table1, 

then I'm getting the result that I exactly want. 

Is there any solution to get the same result through DAX query in PowerBI?

Thank you for your help in advance!

[Daryl-Lynch-Bzy]

Hi @yjk3140 are you using Direct Query connection?  and what is the data source?  It appears that Power BI Data Model does not understand how to convert the DATEDIFF function into Direct Query that you data can understand.  Based on the SQL example you provided, you may have to switch to use a UNIX timefunction like this:

Hour Difference = DIVIDE ( Unixtimestamp1 - Unixtimestamp2 , 3600 )

[yjk3140]

Hi @Daryl-Lynch-Bzy , I'm connecting Direct Query to Databricks SQL Endpoint from PowerBI..

How can I get the Unixtimestmp using DAX? As the timestamps I have are not in the format..

really appreciate your help