Production Time Analysis - Calculate Interval between sequenced Transactions.

[Pr0ton]

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 worth of data, so could be 2.5 million transactions.

I'm trying to calculate the column H 'Interval'.

I have read about a few possible options, using index after grouping and offsetting, a calclated column or measure. 

Ideally the  calculated column  is what i'd like to use, but a measure would be handy also.

Each UID  is unique, but follows a sequence of operations within a batch. Each batch and number of transactions varies. All I really need is to identify the  previous completed transaction for the UID and do the maths using the current start time of the record? Its all within the single table TAnal.

However, I have failed miserably so far, can anyone help or point to a solution somewhere?

Thank you for your support,

Much appreciated,

Proton

[gmsamborn]

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

[Pr0ton]

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]

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.

[amitchandak · Accepted solution]

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

Thanks All,

I think this is now  resolved. 😀

Thanks to @amitchandak 

1. Removed :- Schoolboy error in the data query which had unwanted 'almost duplicate' lines which prevented the Rank from counting  correctly.

2. Tweaked the Datediff so that it uses   [Completed Time] as Date1 , and  Ranked-1 Earlier  [Start Time] as Date 2 (which is actually the subsequent transaction as the ranking increments as it gets younger)

@gmsamborn  Unfortunatley the Measure was too resource hungry for this dataset, but yes, on the sample you generated, works . Much appreciated for your time.

Many thanks again.