topic Re: Difference in value of different rows in same column in DAX Commands and Tips

Difference in value of different rows in same column

anonomi — Mon, 30 Mar 2020 10:36:31 GMT

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

Re: Difference in value of different rows in same column

az38 — Mon, 30 Mar 2020 11:12:55 GMT

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)

Re: Difference in value of different rows in same column

anonomi — Mon, 30 Mar 2020 13:24:24 GMT

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.

Re: Difference in value of different rows in same column

az38 — Mon, 30 Mar 2020 16:03:20 GMT

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

Re: Difference in value of different rows in same column

Anonymous — Mon, 30 Mar 2020 16:24:19 GMT

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