cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Row Value based on criteria and previous value

Hi all,

I have a table with timestamps and unique ids. What i am trying to achieve is:

For each row, look at previous 30 minutes and return "Yes" if there was same Id.

Is there a way to achieve something like this?

Table below shows an example (Not sure if table is 30 minutes, as i took random numbers)

 Time Stamp ID Result 07:46:34 4 No 09:08:38 1 No 09:25:55 4 No 09:37:26 10 No 10:04:48 2 No 10:42:14 5 No 11:08:10 2 No 11:13:55 2 Yes 11:38:24 8 No 11:57:07 7 No 12:17:17 10 No 12:28:48 10 Yes 12:50:24 9 No 13:36:29 3 No 14:15:22 10 No 14:21:07 3 No 16:06:14 3 No 16:20:38 8 No 16:22:05 3 Yes 16:40:48 10 No 17:00:58 5 No 17:08:10 10 Yes 17:13:55 6 Yes 17:29:46 6 Yes

Thank you

Filarap

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Row Value based on criteria and previous value

Hi @filarap ,

To create two calculated column as below.

```index =
RANKX (
FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Time Stamp],
,
ASC,
DENSE
)
```
```Resu =
VAR inx = 'Table'[index] - 1
VAR a =
CALCULATE (
MAX ( 'Table'[Time Stamp] ),
FILTER (
'Table',
'Table'[index] = inx
&& 'Table'[ID] = EARLIER ( 'Table'[ID] )
)
)
VAR b = 'Table'[Time Stamp]
VAR diff =
DATEDIFF ( a, b, MINUTE )
RETURN
IF ( ISBLANK ( diff ) || diff > 30, "no", IF ( diff <= 30, "yes" ) )
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
2 REPLIES 2
Community Support Team

## Re: Row Value based on criteria and previous value

Hi @filarap ,

To create two calculated column as below.

```index =
RANKX (
FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
'Table'[Time Stamp],
,
ASC,
DENSE
)
```
```Resu =
VAR inx = 'Table'[index] - 1
VAR a =
CALCULATE (
MAX ( 'Table'[Time Stamp] ),
FILTER (
'Table',
'Table'[index] = inx
&& 'Table'[ID] = EARLIER ( 'Table'[ID] )
)
)
VAR b = 'Table'[Time Stamp]
VAR diff =
DATEDIFF ( a, b, MINUTE )
RETURN
IF ( ISBLANK ( diff ) || diff > 30, "no", IF ( diff <= 30, "yes" ) )
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Regular Visitor

## Re: Row Value based on criteria and previous value

Thank you

Realy great solution, work like a charm.

Regards

Filarap

Announcements

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

#### Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)