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...
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! 🙂

     

     



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! 🙂