Forum Discussion

JulianaMacedo's avatar
JulianaMacedo
Helper II
4 years ago

Modelling Time Calculation with irregular intervals

Hi all!

 

I have a table that looks like this:

ProductTimeStamp
A12:18:30
A12:18:36
A12:18:41
A12:18:47
A14:28:08
A14:28:13
A14:28:18
A14:28:24
A14: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"
Need Help

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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/339586.
    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's avatar
      JulianaMacedo
      Helper II
      4 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        4 years ago

        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)