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...
OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
9 years ago

Hi Locke

 

Here is a calculated column to get you started - see if it does what you want.

 

It calculates difference from previous timestamp for current id if offercount = 1.

It doesn't explicitly check whether most recent offercount >1, but you could build that in as well.

 

Timestamp difference =
IF (
    'Table'[offercount] = 1,
    VAR Current_timestamp = 'Table'[timestamp]
    RETURN
        Current_timestamp
            - CALCULATE (
                MAX ( 'Table'[timestamp] ),
                ALLEXCEPT ( 'Table', 'Table'[id] ),
                'Table'[timestamp] < Current_timestamp
            )
)