Forum Discussion

NaderSaeed's avatar
NaderSaeed
Helper II
4 years ago
Solved

Lag Column based on different loops and sorting

Hello,   Is there a way to get a Lagged Column for 'Table 1' [Value] (using a calculated column or a measure)  that returns the previous value of the column [Value]? After sorting by [Unit] then...
Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    NaderSaeed , Try a new column like

     

    New column =
    var _max = maxx(filter(Table, [Unit] = earlier( [Unit]) && [Date] < earlier([Date]) && [Parameter] = earlier([Parameter]) ), [Date])
    return
    maxx(filter(Table, [Unit] = earlier( [Unit]) && [Date] =_max && [Parameter] = earlier([Parameter]) ), [Value])

amitchandak's avatar
amitchandak
Super User
4 years ago

NaderSaeed , Try a new column like

 

New column =
var _max = maxx(filter(Table, [Unit] = earlier( [Unit]) && [Date] < earlier([Date]) && [Parameter] = earlier([Parameter]) ), [Date])
return
maxx(filter(Table, [Unit] = earlier( [Unit]) && [Date] =_max && [Parameter] = earlier([Parameter]) ), [Value])

  • NaderSaeed's avatar
    NaderSaeed
    Helper II
    4 years ago

    Hi amitchandak 

    It's giving me a Synatx error 

     

    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      NaderSaeed , Please use correct table name, In power BI if the table name is tbale it is usually in single quote

      'Table'

       

      Also, check if your table name is the same

       

      New column =
      var _max = maxx(filter('Table', [Unit] = earlier( [Unit]) && [Date] < earlier([Date]) && [Parameter] = earlier([Parameter]) ), [Date])
      return
      maxx(filter('Table', [Unit] = earlier( [Unit]) && [Date] =_max && [Parameter] = earlier([Parameter]) ), [Value])