How to extract the values from two consecutive rows

[Nandor]

Hi,

I have measurements of an equipment that produces tablets. The operator logs the value that the machine shows every 15 minutes. This value shows how many tablets were produced from the last reset.

I would like to see how many tablets were produced between two consecutive measurements.

My data looks like this:

Date&Time Measurement

Date&Time	Measurement
01/06/2016 06:00	0
01/06/2016 06:30	12000
01/06/2016 07:00	22000
01/06/2016 07:30	30000
01/06/2016 08:00	44000
01/06/2016 08:30	58000
01/06/2016 09:00	72000
01/06/2016 09:30	80000
01/06/2016 10:00	102000

I would need to add a new column that shows every 30 minutes how many tablets were produced. My table should be like this:

Date&Time	Measurement	Tablets produced between two measurements
01/06/2016 06:00	0	0
01/06/2016 06:30	12000	12000
01/06/2016 07:00	22000	10000
01/06/2016 07:30	30000	8000
01/06/2016 08:00	44000	14000
01/06/2016 08:30	58000	14000
01/06/2016 09:00	72000	14000
01/06/2016 09:30	80000	8000
01/06/2016 10:00	102000	22000

Thank you for the help.

Nandor

[KGrice · Accepted solution]

Hi @Nandor. One way to do it would be to add an Index column to your dataset. While you're in the query editor, you can go to Add Column > Add Index Column. Then you can go to the Data view and Add New Column where you subtract the row where the index is one less the current row. Something like:

Column = TableName[Tablets] - LOOKUPVALUE(TableName[Tablets], TableName[Index], TableName[Index]-1)

Just be sure your data is sorted properly before adding the Index column!

[KGrice · Accepted solution]

Hi @Nandor. One way to do it would be to add an Index column to your dataset. While you're in the query editor, you can go to Add Column > Add Index Column. Then you can go to the Data view and Add New Column where you subtract the row where the index is one less the current row. Something like:

Column = TableName[Tablets] - LOOKUPVALUE(TableName[Tablets], TableName[Index], TableName[Index]-1)

Just be sure your data is sorted properly before adding the Index column!

[Nandor · Accepted solution]

Thank you!