Forum Discussion
Time difference between next row
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?
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 ) )
