Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

datetime difference in datetime format

Hi there !

 

 

I am trying to find the diff b/w these 2 columns . The output should be in datetime. 

Ultimate goal is to show  time difference as x days y hours z minutes w seconds format as the difference.

 

Is there a DAX function available for the same ?

DATEDIFF only gives one value based on HOUR, MINUTES, SECONDS that needs to be modified to finally achieve the above goal.

 

Can anyone help me with this?

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    4 years ago

    Hi, Anonymous 

    You need to create multiple calculated columns to obtain results at each time granularities, and then concatenate the results.

    Duration in Seconds = DATEDIFF('Table'[Datetime1],'Table'[Datetime2],SECOND)
    _day = INT('Table'[Duration in Seconds]/(3600*24))
    _hour = INT( MOD('Table'[Duration in Seconds],(3600*24))/3600)
    _minute = INT(MOD(MOD('Table'[Duration in Seconds],(3600*24)),3600 )/60)
    _second = MOD(MOD(MOD('Table'[Duration in Seconds],(3600*24)),3600 ),60)
    diff = 'Table'[_day] &" days "&'Table'[_hour]&" hours "&'Table'[_minute]&" minutes "&'Table'[_second]&" seconds"

    Result:

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

    You need to create multiple calculated columns to obtain results at each time granularities, and then concatenate the results.

    Duration in Seconds = DATEDIFF('Table'[Datetime1],'Table'[Datetime2],SECOND)
    _day = INT('Table'[Duration in Seconds]/(3600*24))
    _hour = INT( MOD('Table'[Duration in Seconds],(3600*24))/3600)
    _minute = INT(MOD(MOD('Table'[Duration in Seconds],(3600*24)),3600 )/60)
    _second = MOD(MOD(MOD('Table'[Duration in Seconds],(3600*24)),3600 ),60)
    diff = 'Table'[_day] &" days "&'Table'[_hour]&" hours "&'Table'[_minute]&" minutes "&'Table'[_second]&" seconds"

    Result:

     

    Best Regards,
    Community Support Team _ Eason
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.