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

     

Pr0ton's avatar
Pr0ton
Frequent Visitor
2 years ago

Many Thanks, amitchandak. Its close but this is whats being returned.

I added as 2 columns, 1 for Rank, the other Diff? Is this correct?

The rank skips a line here and there, but I can see no missing lines in raw data. Diff is all negative when it dosent skip a rank. 

 

Edited 5 mins later:- There must be missing lines as when i do a count of UID there are 43. I will do some more digging and come back to you. Thanks.

  • gmsamborn's avatar
    gmsamborn
    Super User
    2 years ago

    Hi Pr0ton 

     

    Would a measure like this help?  It seems to work for me.

     

    Interval = 
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 = IF( _PrevClose, _CurrStart - _PrevClose )
VAR _Result = FORMAT( _Diff, "hh:mm" )
RETURN
    _Result

     

     

    Intervals.pbix

     

    Let me know if you have any questions.