topic Re: How to Calculate Time difference from MIN Timestamp and MAX Timestamp in same Column in DAX Commands and Tips

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

jereaallikko — Thu, 12 May 2022 11:31:07 GMT

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

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

Whitewater100 — Thu, 12 May 2022 12:58:23 GMT

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

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

Arul — Thu, 12 May 2022 13:02:25 GMT

Whitewater100 ,

also check out this thread

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

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

Anonymous — Tue, 17 May 2022 08:41:36 GMT

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