Forum Discussion

NaderSaeed's avatar
NaderSaeed
Icon for Helper II rankHelper 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
Icon for Super User rankSuper 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
Icon for Helper II rankHelper II
4 years ago

Hi amitchandak 

It's giving me a Synatx error 

 

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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])