M Query to return time difference between two cells in the same column

Hi Anonymous,

Assuming that the rows are in the correct order, create an index on your table(unless you have something else in your dataset that uniquely identifies your rows which is also a number, then you can use this). This index is used to identify the previous row. You create it by Add Column -> Index Column. 

Then create a custom column with a code like this(note that theM is case sensitive.):

= if 
   #"Added Index"{[Index]} [Index] = 0 then null 
else 
   #"Added Index"{[Index]} [dummyDateTime] - #"Added Index"{[Index]-1} [dummyDateTime]

This code generates a new column where the values are the value of the current row - the value of the previous row. The if-statement is there to prevent an error for the first row where there is no prior row to compare with.

I have created a mockup report here: mockup 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.