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

[jereaallikko]

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

[Whitewater100 · Accepted solution]

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 · Accepted solution]

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

[Arul]

@Whitewater100 ,

also check out this thread

https://community.powerbi.com/t5/Desktop/Sum-timestamp-based-on-Min-Max-and-Sum-all-lines/m-p/201437... 

[Whitewater100 · Accepted solution]

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