Forum Discussion

jereaallikko's avatar
jereaallikko
Icon for Helper III rankHelper III
4 years ago
Solved

How to Calculate Time difference from MIN Timestamp and MAX Timestamp in same Column

Hi all,

 

I am trying to calculate a time difference (ideally on hh/mm/ss format) from different rows from earliest timestamp and latest timestamp.

 

My data contains ID and Timestamp (date/time) columns (and many more). Here is a sample data:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I'm trying to calculate the ddhhmm difference between earliest timestamp and the last one from each ID. So like this:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

So the end result should look like this in HH/MM/SS format, DD/HH/MM/SS would be also nice to have:

 

 

 

 

Any tips?

 

Thanks,

Jere

Need Help DAX
tips & tricks
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    Hi jer:

     

    You could do a few measures.

    MIN Time value = CALCULATE(MIN(Data[Time]), ALLEXCEPT(Data,Data[ID]))
    MAx Time value = CALCULATE(MAX(Data[Time]), ALLEXCEPT(Data,Data[ID]))
    Time Difference H = DIVIDE( DATEDIFF([MIN Time value],[MAX Time value],MINUTE),60)
     
    Here's the result using some of my data:(Please note format is LongTime)

    My Data table named Data

     

     
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi jereaallikko ,

     

    Please check this measure.

    Measure = 
VAR min_ = CALCULATE(MIN('Table'[timestamp]),ALLEXCEPT('Table','Table'[ID]))
VAR max_ = CALCULATE(MAX('Table'[timestamp]),ALLEXCEPT('Table','Table'[ID]))
var vSeconds=DATEDIFF(min_,max_,SECOND)
var vMinutes=int( vSeconds/60)
var vRemainingSeconds=MOD(vSeconds, 60)
var vHours=INT(vMinutes/60)
var vRemainingMinutes=MOD(vMinutes,60)
var vDays=INT(vHours/24)
var vRemainingHours=MOD(vHours,24)
return
vDays&" "&
  vRemainingHours&":"&
  vRemainingMinutes&":"& 
  vRemainingSeconds

     

    Best Regards,

    Jay

3 Replies

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi jer:

     

    You could do a few measures.

    MIN Time value = CALCULATE(MIN(Data[Time]), ALLEXCEPT(Data,Data[ID]))
    MAx Time value = CALCULATE(MAX(Data[Time]), ALLEXCEPT(Data,Data[ID]))
    Time Difference H = DIVIDE( DATEDIFF([MIN Time value],[MAX Time value],MINUTE),60)
     
    Here's the result using some of my data:(Please note format is LongTime)

    My Data table named Data

     

     
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi jereaallikko ,

     

    Please check this measure.

    Measure = 
VAR min_ = CALCULATE(MIN('Table'[timestamp]),ALLEXCEPT('Table','Table'[ID]))
VAR max_ = CALCULATE(MAX('Table'[timestamp]),ALLEXCEPT('Table','Table'[ID]))
var vSeconds=DATEDIFF(min_,max_,SECOND)
var vMinutes=int( vSeconds/60)
var vRemainingSeconds=MOD(vSeconds, 60)
var vHours=INT(vMinutes/60)
var vRemainingMinutes=MOD(vMinutes,60)
var vDays=INT(vHours/24)
var vRemainingHours=MOD(vHours,24)
return
vDays&" "&
  vRemainingHours&":"&
  vRemainingMinutes&":"& 
  vRemainingSeconds

     

    Best Regards,

    Jay