cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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 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?

Much appreciated,

Proton

1 ACCEPTED SOLUTION
Super User

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

5 REPLIES 5
Super User

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

Frequent Visitor

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.

Super User

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.

Super User

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

Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors