Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating previous row with earlier function

Hello,

 

I have the date, user, value columns. In a date, I can have many users and the value for each user should grow.

I need to know when a value is smaller for a later date. I cannot use index because I have several users the same date so my data will not be ordered. I am trying to use a EARLIER function but it is not working well. Could anyone help me?

 

 

The code I am using is: 

MAXX(FILTER('Table','Table'[USER]=EARLIER('Table'[USER]) && 'Table[VALUE] <= EARLIER( 'Table[VALUE]),1)
 
Thanks in advance
 
 
Need Help
  • rohit_singh's avatar
    rohit_singh
    4 years ago

    Hi Anonymous ,

    No problem. Here you go


    You just need to change the variables _curr and _rank and it will work fine.

    var _curr = MinVal[Value]

    var _rank = MinVal[Rank]
     

    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

     

     



4 Replies

  • rohit_singh's avatar
    rohit_singh
    Solution Sage
    4 years ago

    Hi Anonymous ,

    First create a calculated column to calculate rank. This will rank your dates in order by user and will make your calculation very easy. 

    Rank =

    RANKX(
    FILTER(MinVal, MinVal[User] = EARLIER(MinVal[User])),
    MinVal[Date],
    ,
    ASC,
    Dense
    )

     

    Next, create a measure to flag the row where the value is less than previous row. Since you have created the rank in the previous step, you can simply filter by rank-1 to get previous row.

     

    ValueFlag =

    var _curr = SUM(MinVal[Value])

    var _rank = max(MinVal[Rank])

    var _prev =
    CALCULATE(
    SUM(MinVal[Value]),
    FILTER(
    ALLEXCEPT(MinVal, MinVal[User]),
    MinVal[Rank] = _rank-1)
    )

    var _cmp = if(_prev > _curr , 1 , 0)
    RETURN
    if(isblank(_prev), 0, _cmp)


    Kind regards,

    Rohit


    Please mark this answer as the solution if it resolves your issue.
    Appreciate your kudos! 🙂

  • NickolajJessen's avatar
    NickolajJessen
    Solution Sage
    4 years ago

    This here grants you the lowest future value

    Then you can add conditionaly formatting for whatever you are looking to do with it 🙂 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks for your answer Rohit. This solution works for a measure but I need to apply it in a column, for a column all values are 0.

    • rohit_singh's avatar
      rohit_singh
      Solution Sage
      4 years ago

      Hi Anonymous ,

      No problem. Here you go


      You just need to change the variables _curr and _rank and it will work fine.

      var _curr = MinVal[Value]

      var _rank = MinVal[Rank]
       

      Kind regards,

      Rohit


      Please mark this answer as the solution if it resolves your issue.
      Appreciate your kudos! 🙂