Forum Discussion
Solved
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...
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 ) )
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 ) )Thanks,
Your method works well in small table, but in my case i have for now 22k rows and when i tried to do this i'm getting information:
"There's not enough memory to complete this operation. Please try again later when there may be more memory available."
Should it need so much power to calculate that?
