Forum Discussion

Pr0ton's avatar
Pr0ton
Frequent Visitor
2 years ago
Solved

Production Time Analysis - Calculate Interval between sequenced Transactions.

I would like to calculate the interval between Transactions. See the example table below. I'm looking for some trends on 'intervals' or 'waiting times' , and would like to look at a years wort...
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    Pr0ton ,

    Create the following column

    Rank = Rankx(filter(Table, Table[UID] = earlier([UID]) ), [Start Time])

     

    diff = if([Rank] <>1, datediff(Maxx(filter(Table, Table[UID] = earlier([UID]) && Table[Rank] = earlier([Rank]) -1), [Start Time]) , [Start Time], second) )

     

gmsamborn's avatar
gmsamborn
Super User
2 years ago

Hi Pr0ton 

 

I realized that my previous measure didn't handle intervals greater than 24 hrs.

 

Again, this seems to work.

 

Interval 2 = 
VAR _CurrStart = SELECTEDVALUE( 'Data1'[Start Time] )
VAR _PrevClose =
    CALCULATE(
        MAX( 'Data1'[Completed Time] ),
        FILTER(
            ALLEXCEPT( 'Data1', 'Data1'[UID] ),
            'Data1'[Start Time] < MAX( 'Data1'[Start Time] )
        )
    )
VAR _Diff = CONVERT( IF( _PrevClose, _CurrStart - _PrevClose ), DOUBLE )
VAR _Days = INT( _Diff )
VAR _Remainder = _Diff - _Days
VAR _Hrs = INT( _Remainder * 24 )
VAR _Mins = INT( ( _Diff - ( ( _Days * 24 ) +  _Hrs ) * 60 / 1440 ) * 1440 ) + 1
VAR _Result =
    IF(
        _Diff,
        _Days & " d " & 
        _Hrs & " h " & 
        _Mins & " m"
    )
RETURN
    _Result

 

 

Intervals - 2.pbix