Modelling Time Calculation with irregular intervals

[JulianaMacedo]

Hi all!

I have a table that looks like this:

Product	TimeStamp
A	12:18:30
A	12:18:36
A	12:18:41
A	12:18:47
A	14:28:08
A	14:28:13
A	14:28:18
A	14:28:24
A	14:28:30

For each product, I need to calculate the total operating time, the problem is that there are irregular intervals and it is difficult to track the beggining and end of each interval. Unfortunately the granularity of seconds it is needed in this case, so I need to keep it.

Anyone have any suggestion on how to sum up the seconds and minutes for every hour? I'm thinking that could be the best solution, get the sum of seconds and minutes per hour and then sum all these results per hour for a whole day, month etc.

What I was trying to do is not considering these jumps, not either the intervals so of course, I got the wrong results:

Total Time = 
VAR totalseconds = 
    SUMX(
        production,
        Hour(production[Time]) * 3600 + MINUTE(production[Time]) *60 + second(production[Time])
    )
var vMinutes=int( totalseconds/60)
var vRemainingSeconds=MOD(totalseconds, 60)
var vHours=INT(vMinutes/60)
var vRemainingMinutes=MOD(vMinutes,60)
var vDays=INT(vHours/24)
var vRemainingHours=MOD(vHours,24)
return
  vDays&" Days & "&
  vRemainingHours&" Hours & "&
  vRemainingMinutes&" Minutes & "& 
  vRemainingSeconds& " Seconds"

[Greg_Deckler]

@JulianaMacedo Not clear what your expected output should be. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

[JulianaMacedo]

Ok Hi!

Thanks for answering, I think it was also a bit unclear to me what should I do, but I got this information now:

I need basically to calculate the total time between the beggining of the interval and the end of the interval per hour. For every hour, take the first timestamp and then the last and calculate how many minutes/seconds.
I'll take a look at the article and see if that helps, thanks for the collaboration.

[Greg_Deckler]

@JulianaMacedo Right, you can use MINX and MAXX to get the minimum and maximum timestamps by using a FILTER for HOUR. So,

MinTS = 

  VAR __Hour = HOUR([timestamp])

RETURN

  __Min = MINX(FILTER('Table',HOUR([timestamp] = __Hour)

[JulianaMacedo]

Hi @Greg_Deckler 

i tried your solution but I can't figure it out why I can't select the columns I need to form the first variable... do you have any idea?

If I type the name of the table it does not bring it as a suggestion, it shows only the other measures created before:

Then, If I do exactly the same in the same HOUR function inside the function MINX, then it comes as a suggestion the columns I'm looking for, but not in the first variable.

The same thing happened when I tried to use EALIER.
Any ideas why?

[JulianaMacedo]

I think it might be something in that direction, but it's not helping actually....maybe if I try to clarify better:

I'm trying to working on a measure that will do the following:
Measure = 
Calcuate(
difference between the lastest timestamp and the earliest time stamp), 
filter (by hour))

I was trying to mix your answer with the following code, i tried different sintax that did not work:

MaxHour = 
CALCULATE (
    MAX ( 'Table'[Column to calculate] ),
    FILTER (
        'Table',
        'Table'[Date] = EARLIER ( 'Table'[Date] )
            && HOUR ( 'Table'[Time] )
                = HOUR ( EARLIER ( 'Table'[Time] ) )
    )
)