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 [Date] and then [Parameter] columns?

 

Date Unit Parameter Value Expected Lag Column
11/3/21 7:04 AM A Sensor 3 0
11/3/21 7:03 AM B Sensor 5 0
11/3/21 7:48 AM A Camera 1.3 0
11/3/21 7:49 AM A Sensor 4 3
11/3/21 8:06 AM A Pulse 0.2 0
11/3/21 10:30 AM B Pulse 0.3 0
11/3/21 7:50 AM A Camera 1.25 1.3
11/3/21 11:17 AM B Code 3 0
11/3/21 11:23 AM A Code 2.7 0
11/3/21 11:19 AM B Code 3 3
11/3/21 11:23 AM A Sensor 5 4
11/3/21 7:49 PM B Camera 1.6 0
11/3/21 7:55 PM A Camera 1.3 1.25
11/3/21 8:20 PM B Camera 1.56 1.6
11/3/21 1:27 PM B Pulse 0.1 0.3
11/3/21 9:09 PM A Pulse 0.25 0.2
11/3/21 7:59 AM B Sensor 5 5
11/3/21 11:28 AM A Code 2.55 2.7

 

Thank you!

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

3 Replies

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

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