Forum Discussion

Taro_Gulat's avatar
Taro_Gulat
Regular Visitor
1 year ago
Solved

Dynamic Timestamp Difference

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

Need Help
  • johnt75's avatar
    johnt75
    1 year ago

    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's avatar
    Anonymous
    1 year ago

    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.

     

2 Replies

  • johnt75's avatar
    johnt75
    Super User
    1 year ago

    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's avatar
    Anonymous
    Not applicable
    1 year ago

    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.