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 · Accepted solution]

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
)

[Anonymous · Accepted solution]

Hi @Taro_Gulat ,

I think you can also try measure to achieve your goal.

MEASURE =
VAR _VirtualTable =
    ADDCOLUMNS (
        FILTER ( 'Table', 'Table'[Item] = "B" && 'Table'[Type] = "X" ),
        "DateDiff",
            VAR _CurrentDateTime = [Date]
            VAR _MINDatetime =
                MINX (
                    FILTER (
                        ALL ( 'Table' ),
                        'Table'[Item] = "C"
                            && 'Table'[Type] = "X"
                            && 'Table'[Date] > _CurrentDateTime
                    ),
                    'Table'[Date]
                )
            RETURN
                DATEDIFF ( [Date], _MINDatetime, MINUTE )
    )
RETURN
    AVERAGEX ( _VirtualTable, [DateDiff] )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[johnt75 · Accepted solution]

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
)