Calculating previous row with earlier function

[Anonymous]

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

[Anonymous]

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 · Accepted solution]

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

[NickolajJessen]

This here grants you the lowest future value

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

[rohit_singh]

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