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 )
    )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

pak

 

You are adding it as a calculated column.

 

PLease add it as a MEASURE

pak's avatar
pak
Helper II
8 years ago

Thanks Zubair_Muhammad  - for your time and patience :)

 

Rookie mistake! :)

 

It would be very helpfull for me in the future!

 

 

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    pak

     

    So did it work???... No memory issues??

     

    :smileyvery-happy:

  • pak's avatar
    pak
    Helper II
    8 years ago

    Zubair_Muhammad

     

    With even 1000 of rows it is ok, but when i tried to do this with all data (for now it's over 30000 rows) it was using 100% of my memory for 10minutes with no result. I had to kill the process because i was unable to do anything on my PC.

     

    Maybe it is because we take MIN from all rows from table with every row calculation?

     

    Maybe i'll add INDEX column and next row will be INDEX+1 row, so there is no need to calucate all?

     

    Also with Time Diference Measure i was unable to SUM all time difference, and i have to do so.

  • sukram's avatar
    sukram
    Regular Visitor
    7 years ago

    pak 

     

    Did your mehtod with INDEX work to reduce memory consumption? If yes, how exactly did you realize it?

     

    Thank you very much