Forum Discussion

pak's avatar
pak
Helper II
8 years ago
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...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    pak

     

    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 )
    )
pak's avatar
pak
Helper II
8 years ago

Hi Zubair_Muhammad

 

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?

Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

pak

 

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 )
    )