Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

create a new column that calculate the difference between current row and the adjacent row

i'm trying to create a new column that shows the subtracted difference between the timestamp on the current row

vs 

the adjecnt row right below it.

 

is this possible in Powerbi?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    Create an index column to the table and then create the calculated column as below.

    DIFF = 
var _lasttime = CALCULATE(MAX('Table'[timestamp]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))
return
DATEDIFF(_lasttime,'Table'[timestamp],SECOND)

     

    Best Regards,

    Jay

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

     

    Create an index column to the table and then create the calculated column as below.

    DIFF = 
var _lasttime = CALCULATE(MAX('Table'[timestamp]),FILTER('Table','Table'[Index]=EARLIER('Table'[Index])-1))
return
DATEDIFF(_lasttime,'Table'[timestamp],SECOND)

     

    Best Regards,

    Jay

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      created the index column in the table,

      but during the calculated column process it throw me an error

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi Anonymous ,

         

        A parenthesis is missing at the end of the variable definition.

         

        Best Regards,

        Jay

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , New column

     

    new column
    = [timestamp] - maxx(filter(Table, [timestamp] < earlier([timestamp])),[Timestamp])

     

    or


    new column
    = [timestamp] - maxx(filter(Table, [timestamp] < earlier([timestamp]) && [project] = earlier([project])),[Timestamp])

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Amitchandak,

       

      I tried the calculated columns few times, Powerbi just kept spining, i think the data have to many rows for it to calculate? it has more than a million rows.

      is there other ways to do this?