cancel
Showing results for
Did you mean:
Regular Visitor

## 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?

Tanja

3 REPLIES 3
Microsoft

@Locke

Have you tried above solutions?

Best Regards,

Herbert

Microsoft

@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

Super User I

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

Owen Auger

My Blog

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.