Forum Discussion

yjk3140's avatar
yjk3140
Helper I
3 years ago

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

 

 

 

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      yjk3140
      Helper I
      3 years ago

      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!

       
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        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 )