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 change. To give a short example table:

 

id | timestamp | offercount

123 | 13.10.2016 12:00 | 1

123 | 13.10.2016 11:00 | 2

123 | 13.10.2016 10:00 | 1

123 | 13.10.2016 9:30 | 2

456 | 13.10.2016 11:00 | 1

456 | 13.10.2016 10:30 | 3

...

 

 

What I would like to do, is calculate the time difference, when the status changes from >1 to 1. 

123 | 13.10.2016 12:00 | 1 | 1:00

123 | 13.10.2016 11:00 | 2

123 | 13.10.2016 10:00 | 1 | 0:30

123 | 13.10.2016 9:30 | 2 

456 | 13.10.2016 11:00 | 1 | 0:30

456 | 13.10.2016 10:30 | 3

...

 

The idea was to use the earlier funtion and combine this with Datediff. But I can't get the right function. Any ideas?

 

 

My other solution was having two tables: one where the offercount is always 1 and the other, where the count is >1. Then calculate with the max values: = CALCULATE(MAX('AMZ_1'[time])-max('AMZ_>1'[time]))

The hours seem to match. But the days are not taken into account. And maybe not a very elegant solution.

 

 

Any ideas or tips?

 

Thanks in advance!

Tanja

 

 

3 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft 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

  • OwenAuger's avatar
    OwenAuger
    Super 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
            )
)