If last two values have a difference of more than 2, then do something?

[grggmrtn]

There should be an easy fix for this, but I'm not quite sure how to do it.

The data is simple: Name, date, value:

Bob	01-01-2020	99
Bob	01-03-2020	98
Bob	01-05-2020	96
Jane	02-01-2020	77
Jane	02-02-2020	76
Jane	02-03-2020	78
Kenneth	01-06-2020	66

Because the difference for Bob's last two values is >= 2, I need to flag it somehow. Jane and Kenneth don't need anything, because Jane's last 2 values have a difference of -2 and since it's a rise it doesn't need to be flagged, and Kenneths value can't be compared to anything because there's only the one.

It's ONLY the last two values that are important. For example if Bob gets updated on 01-06-2020 and the value is 95, he no longer needs to be flagged.

The end result should be a list of names that are flagged.

I'd appreciate any help I can get, thanks!

[v-lionel-msft · Accepted solution]

Hi @grggmrtn ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@grggmrtn , Create a new column that will flag for that row, You can filter a get name

Column = var _1  = maxx(FILTER('Table','Table'[Name]= EARLIER('Table'[Name]) && 'Table'[Date] < EARLIER('Table'[Date])),LASTNONBLANKVALUE('Table'[Date],'Table'[Value])) return If(ISBLANK(_1), BLANK(),if( _1 -[Value] >=2,1,0))

file is attached after signature

[grggmrtn]

Thanks for this @amitchandak - it's almost exactly what I'm looking for 🙂

The problem comes since your column is looking at all dates instead of just the last two. Here's an example from my data (the colours indicate three different names in this column)

If I filter this list by [Column] then "blue" will also be in this list, even though the difference between their values of the last two dates < 0. So my flags won't tell me what the situation is TODAY when I'm looking at the report. According to the above data, only "yellow" should the only one that has a 1.

Make any sense?