Time calculation based on different rows -> earlier?

[Locke]

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

[v-haibl-msft]

@Locke

Have you tried above solutions?

Best Regards,

Herbert

[v-haibl-msft]

@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]

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
            )
)