Forum Discussion

JulianaMacedo's avatar
JulianaMacedo
Icon for Helper II rankHelper II
4 years ago

Modelling Time Calculation with irregular intervals

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 1...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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
    Icon for Helper II rankHelper 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
      Icon for Community Champion rankCommunity 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)

       

       

       

       

      • JulianaMacedo's avatar
        JulianaMacedo
        Icon for Helper II rankHelper II
        4 years ago

        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] ) )
    )
)