Forum Discussion
Time difference between next row
Hi all,
I have another question,
A want to to count time difference between actual row and next row, when "bit" equals 1, like beneath:
| Date/Time | Bit | Time Difference |
| 2018-01-11T17:03:00 | 1 | 10 |
| 2018-01-11T17:03:10 | 1 | 10 |
| 2018-01-11T17:03:20 | 1 | 4 |
| 2018-01-11T17:03:24 | 1 | 10 |
| 2018-01-11T17:03:34 | 1 | 10 |
| 2018-01-11T17:03:44 | 1 | 10 |
| 2018-01-11T17:03:54 | 1 | 6 |
| 2018-01-11T17:04:00 | 0 | |
| 2018-01-11T17:04:10 | 0 | |
| 2018-01-11T17:04:16 | 0 |
There is no function for next row in table. I've tried different functions but always with error or 0 value in TimeDifference.
I also added two index column. The difference between them is: Index.1=Index + 1. Tried to use it but i have syntax error:
I made this using method from this tutorial:
https://www.youtube.com/watch?v=xN2IRXQ2CvI&app=desktop
But maybe there is another, better way?
What will happen when i delete column used to merge tables, when my data is live streamed from IoT Hub to Azure StreamAnalytics to TableStorage, to Power BI. Would it work for another rows after?
May be a MEASURE instead of a column would handle memory better.
Try this MEASURE
Time Difference Measure = VAR NextRow = CALCULATE ( MIN ( TableName[Date/Time] ), FILTER ( ALL ( TableName ), TableName[Date/Time] > SELECTEDVALUE ( TableName[Date/Time] ) ) ) RETURN IF ( SELECTEDVALUE ( TableName[Bit] ) = 1, DATEDIFF ( SELECTEDVALUE ( TableName[Date/Time] ), NextRow, SECOND ) )
14 Replies
Have a look at this article as it does something very similar. The trick is to use EARLIER.
You may need to use MAX instead of MIN, just depends on which direction you want to go.
Hi pak
If your Column is formatted as Date/Time, then this calculated column will get you the desired result
Time Difference = VAR NextRow = CALCULATE ( MIN ( TableName[Date/Time] ), FILTER ( ALL ( TableName ), TableName[Date/Time] > EARLIER ( TableName[Date/Time] ) ) ) RETURN IF ( TableName[Bit] = 1, DATEDIFF ( TableName[Date/Time], NextRow, SECOND ) )
