Dynamic Timestamp Difference

[Taro_Gulat]

Hi all, 

I am having some difficulty in Dax with the below scenario:

I need to calculate the difference in datetime between item = B, Type = X and item = C, Type = first available X. Example: Difference between row number 2 and 4, result is 1 minute then difference between row number 7 and 9, result is 1 minute and difference between row number 12 and 14, result is 1 minute. Once all differences are calculated an average will be required based on based on number of results.

I tried with calculated columns but not giving me desired results. 

Can anyone help?

Thanks

[johnt75]

You could create a calculated column like

Time difference =
IF (
    'Table'[Item] = "B"
        && 'Table'[Type] = "X",
    VAR CurrentTime = 'Table'[Time]
    VAR NextAvailableTime =
        MINX (
            FILTER (
                ALL ( 'Table'[Item], 'Table'[Type], 'Table'[Time] ),
                'Table'[Item] = "C"
                    && 'Table'[Type] = "X"
                    && 'Table'[Time] > CurrentTime
            ),
            'Table'[Time]
        )
    VAR Result =
        DATEDIFF ( CurrentTime, NextAvailableTime, MINUTE )
    RETURN
        Result
)