Forum Discussion

Locke's avatar
Locke
Regular Visitor
9 years ago

Time calculation based on different rows -> earlier?

Hi Guys,   I have read the post on the earlier function, but can't seem to be able to find the correct formula for my case.   I have a Table with offer changes, each row represents a status chang...
v-haibl-msft's avatar
v-haibl-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Locke

 

You can try to create an index column in Query Editor and then a calculated column with following formula. LOOKUPVALUE and DATEDIFF are used here, and the time unit here is hour.

 

 

time difference = 
IF (
    'Table'[offercount] = 1
        && LOOKUPVALUE ( 'Table'[offercount], 'Table'[Index], 'Table'[Index] + 1 )
            > 1,
    DATEDIFF (
        LOOKUPVALUE ( 'Table'[timestamp], 'Table'[Index], 'Table'[Index] + 1 ),
        'Table'[timestamp],
        SECOND
    )
        / 3600
)

 

Best Regards,

Herbert