Absolute Value Difference Between Two Non-Consecutive Rows (Same IDs)

[samdep]

Hi PBI Community!

I have a table similar to the below:

DATE             ID          GPA  

12/1/2021    ABCD     3.8

12/1/2021    ABCD     4.0

I am using tables in my visual and would like to include a flag for any variance in the GPA greater than 1.5 between the same ID/Student's GPA. So, in this case, ID: ABCD would have a variance of .2 -- The GPA's could come in on the same date, which is why I am having a bit of a struggle. Also, I'd like the absolute value, as opposed to perhaps a negative value.

I may put the .2 in the table itself or write a conditional expression that says something along the lines of 'if the variance is >= 1.5, then Flag, ""'

Thanks All - Very much appreciate all of the help provided in this community!

[amitchandak · Accepted solution]

@samdep , This bit difficult. You need to add an index column in power query -

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

Then create a measure like 

New column =
var _max = maxx(filter(Table, [Index] <earlier([Index])) , [Index])
return
[GPA] - maxx(filter(Table, [Index] = _max ) , [GPA])

or

New column =
var _max = maxx(filter(Table, [Index] <earlier([Index]) && [ID] = earlier([ID] )) , [Index])
return
[GPA] - maxx(filter(Table, [Index] = _max && [ID] = earlier([ID] ) ) , [GPA])

[samdep]

Awesome, thank you! This worked well for me - Index tip was great, hadn't noticed that in PQ. Very helpful!