Difference in value of different rows in same column

[anonomi]

I have a table with a series of values for different devices and on different dates. I would like a calcualted column to subtract each value from the previous one (to measure the increase) based on the date it was received and for each device. I've found similar questions but I couldn't apply them to my problem (as dates weren't plus 1 and there are multiple devices).  I have already added a rank as I thought that would help with the filtering, but I'm still struggling.

Device	Date	Date_Rank	Value	Desired Value
A	14/09/2019	1	10	2
A	15/08/2019	2	8	6
A	12/06/2019	3	2
B	05/03/2020	1	22	12
B	01/02/2020	2	10

[az38 · Accepted solution]

Hi @anonomi 

try

Desired Value = 
var _prevRank = CALCULATE(MIN('Table'[Date_Rank]),FILTER(ALL('Table'),'Table'[Device]=EARLIER('Table'[Device]) && 'Table'[Date_Rank] > EARLIER('Table'[Date_Rank])))
var _prevValue = LOOKUPVALUE('Table'[Value],'Table'[Device],[Device],'Table'[Date_Rank],_prevRank)
return
IF(ISBLANK(_prevValue), BLANK(), [Value]-_prevValue)

[anonomi]

I'm getting the error "A table of multiple values was supplied where a single value was expected". If I reduce the dataset down to a dozen devices then it does work, so I'm assuming its my data thats the issue! Would you mind explaining the LookupValue line as there are more arguments in there than I'm used to.  That might help me diagnose the issue with the dataset.

[az38]

@anonomi 

do you create a column or measure? it's a column

this LOOKUP is trying to find Value with selected device and the next rank which you created. maybe you have rows with the same pair device-rank

[Anonymous]

LOOKUPVALUE is a very slow function. Using it on a fact table will bring the calculation to a halt.

It's kind of good for small dimensions but not for a fact table.

Best
D