Calculating time until the shot

rebrow31 

Create a new column for DateTime:

DateTime = [DATE] & " " & [TIME]

 

Sort your data by Machine, Part, and DateTime.

 

Create an index column:

Index = RANKX(FILTER('Table', 'Table'[MACHINE] = EARLIER('Table'[MACHINE]) && 'Table'[Part] = EARLIER('Table'[Part])), 'Table'[DateTime], , ASC, DENSE)

 

Create a calculated column for the time difference:

TimeDifference =
VAR CurrentIndex = 'Table'[Index]
VAR PreviousDateTime = CALCULATE(MAX('Table'[DateTime]), FILTER('Table', 'Table'[MACHINE] = EARLIER('Table'[MACHINE]) && 'Table'[Part] = EARLIER('Table'[Part]) && 'Table'[Index] = CurrentIndex - 1))
RETURN
DATEDIFF(PreviousDateTime, 'Table'[DateTime], SECOND)

 

Create a measure for the average time difference:

AverageTimeDifference = AVERAGEX('Table', 'Table'[TimeDifference])

Hi rebrow31 ,

Thank you for being part of the Microsoft Fabric Community. bhanu_gautam ,shared valuable insights, and I’ve tested the solution with sample data it meets your requirements exactly.  For your convenience, I’ve attached the .pbix file with the full implementation.

 

Step1: Create a DateTime Column

DateTime = 'Table'[DATE] & " " & 'Table'[TIME]

Step2: Create an Index Column

In the Modeling tab, add a calculated column

Index = 
RANKX(
    FILTER(
        'Table', 
        'Table'[MACHINE] = EARLIER('Table'[MACHINE]) && 
        'Table'[Part] = EARLIER('Table'[Part])
    ), 
    'Table'[DateTime], 
    , 
    ASC, 
    DENSE
)

Step3: Calculate the Time Difference Between Shots

TimeDifference = 
VAR CurrentIndex = 'Table'[Index]
VAR PreviousDateTime = 
    CALCULATE(
        MAX('Table'[DateTime]), 
        FILTER(
            'Table', 
            'Table'[MACHINE] = EARLIER('Table'[MACHINE]) && 
            'Table'[Part] = EARLIER('Table'[Part]) && 
            'Table'[Index] = CurrentIndex - 1
        )
    )
RETURN
IF(
    ISBLANK(PreviousDateTime), 
    BLANK(), 
    DATEDIFF(PreviousDateTime, 'Table'[DateTime], SECOND)
)

Step4: Create a measure to find the average of the time differences

AverageTimeDifference = AVERAGEX('Table', 'Table'[TimeDifference])

 

Output:

 

 

If my response solved your query, please mark it as the Accepted solution to help others find it easily. And if my answer was helpful, I'd really appreciate a 'Kudos'.