How to get difference of value between row and previous row and so on...

[kowalewski92]

Hi,

Maybe anyone know that... how can I count difference between row na previous row and so on...? I have that table:

https://imgur.com/a/c6jLo

I want to have column that give me difference between rows in fuellevel column but first I want to sort all data by column named as deviceid:

https://imgur.com/a/c6jLo

How can I do that?

[v-jiascu-msft · Accepted solution]

Hi @kowalewski92,

Since I don't know the other part of your data, I think adding an index would be helpful. In the Query Editor, sort the data by column deviceid, then add an index column. Finally you can add a calculated column to get the result.

difference =
VAR previous =
    CALCULATE (
        SUM ( table1[fuellevel] ),
        FILTER ( table1, table1[Index] = EARLIER ( table1[Index] ) - 1 )
    )
RETURN
    [fuellevel] - previous

Best Regards,

Dale

[Ivan4ever84]

...if you have a date column in the table, you can ignore the index and use this based on previous day in the formula

... = EARLIER ( table1[Date] ) - 1 )

[kowalewski92]

Hi, thank you. I have another question. I prepare formula according to your information:

https://imgur.com/jtb4fEJ

I got:

https://imgur.com/8xAZD8W

But I want to SUM values that have difference in time about 5 minutes (marked as yellow and red). I have car fueling values so you understand. 🙂

Maybe you know how can i Do that?

[v-jiascu-msft]

Hi @kowalewski92,

You are welcome. I would suggest opening a new thread in this forum due to it's a different question. The point is how you will evaluate the values in 5 minutes.

1. If you want to group the time by 5 minutes, you need to add a new column to identify which 5 minutes the old times belong to.

Old time                     groups

05.11.2017 9:44:15     05.11.2017 9:45:00

05.11.2017 9:46:10     05.11.2017 9:45:00

05.11.2017 9:50:19     05.11.2017 9:45:00

2. If you want to accumulate the previous 5 minutes, that would be a little complex.

Best Regards,

Dale