Calculating time until the shot

[rebrow31]

Hi, 

I have a list of several machines, and their shot date/times. Sample data is below: 

I would like to calculate the time between each shot so I can do an average. For example, machine 1 had a shot time of 9 seconds, first, then 8 seconds etc. How do I calculate the times between the shots by both machines and part. I then want to gether these times and be able to average it out. 

thanks

[bhanu_gautam · Accepted solution]

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

[V-yubandi-msft · Accepted solution]

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'.

[bhanu_gautam · Accepted solution]

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