Forum Discussion
Calculating difference between two timestamps using DAX
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-03...
Hi Anonymous 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!
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 )