Row Value based on criteria and previous value

[filarap]

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

[Anonymous]

Give a try using these instruction:

Table.Group(yourTab, {"ID"}, {{"Conteggio", each Table.RowCount(_), type number}, {"res", each List.Transform(delta(_[Time Stamp]),each Duration.TotalMinutes(_)<30 ))

"delta" is a function defined as follow:

let
diff= (Lista as list, optional def) =>
[shLista= {def}&List.RemoveLastN(Lista,1) ,
LT=List.Transform(List.Zip({Lista,shLista}), each _{0}-_{1})][LT]
in
diff

[Anonymous]

---

@Anonymous wrote:

Give a try using these instruction:

 Table.Group(yourTab, {"ID"}, {{"Conteggio", each Table.RowCount(_), type number}, {"res", each List.Transform(delta(_[Time Stamp]),each Duration.TotalMinutes(_)<30 ))

"delta" is a function defined as follow:

let
diff= (Lista as list, optional def) =>
[shLista= {def}&List.RemoveLastN(Lista,1) ,
LT=List.Transform(List.Zip({Lista,shLista}), each _{0}-_{1})][LT]
in
diff

---

Is, of course, important that the list is sorted, which is implicitly assumed in the previuos script.

Here a differente way to get the same result:

let
NotToOld= (Lista as list,Old as number, optional def) =>
let
LstAcc=List.Skip(List.Accumulate(List.Sort(Lista),[o=def,d={def}],(s,c)=> [o=c,d=s[d]&{Duration.TotalMinutes(c-s[o])<=Old}])[d])
in
LstAcc
in
NotToOld

PS

 I would like to attach a file, but I don't know if it's possible and how to do it

[v-frfei-msft · Accepted solution]

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

[filarap]

Thank you v-frfei-msft 

Realy great solution, work like a charm.

Regards

Filarap