Forum Discussion
Subtract the current value from the previous value?
Hello everyone
I've been struggling with a calculated column.
I have a table that looks like this:
I'm trying to subtract the current value from the previous value so I get a new column that looks like this:
So for example:
17.1 - 16.8 = 0.3
16.8 - 16.2 = 0.6
and so on..........
I have tried using an EARLIER funktion but with no luck.
I look forward to hearing from you :)
Hi,
Does this work?
Data[Value]-LOOKUPVALUE(Data[Value],Data[Timestamp],CALCULATE(MIN(Data[Timestamp]),FILTER(Data,Data[Timestamp]>EARLIER(Data[Timestamp])&&Data[UnitID]=EARLIER(Data[UnitID]))),Data[UnitID],Data[UnitID])
13 Replies
Hi M-M-P
You say I'm trying to subtract the current value from the previous value but the example you're subtracting the next value from the current one. Which one is it? I'll assume the latter:
NewCol = VAR NextTimeS_ = CALCULATE ( MIN ( Table1[Timestamp] ), Table1[Timestamp] > EARLIER ( Table1[Timestamp] ) ) VAR NextVal_ = CALCULATE ( DISTINCT ( Table1[Value] ), Table1[Timestamp] = NextTimeS_ ) VAR CurrentVal_ = Table1[Value] RETURN CurrentVal_ - NextVal_Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi AlB
Many thanks for the reply.
Sorry, it is me who is sloppy. The table in the post shows it right.
Your DAX looks good. But there is a slight problem with it. Right now it just copys the Value column over.
And when I look into it, I get this error:
A circular dependency was detected: Table1 [NextTimeS_], c47c7881-26c8-47b3-8091-79e20cdb8f1d, Table1 [NextTimeS_].Do you have an idea of what this might be?
Thanks
Try this. If it doesn't work please share a sample of your table in text-tabular form in addition to (or instead of) the screen captures. A screen cap doesn't allow people to readily copy the data and run a quick test and thus decreases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here.
NewCol = VAR NextTimeS_ = CALCULATE ( MIN ( Table1[Timestamp] ), Table1[Timestamp] > EARLIER ( Table1[Timestamp] ),
ALL(Table1) ) VAR NextVal_ = CALCULATE ( DISTINCT ( Table1[Value] ), Table1[Timestamp] = NextTimeS_ , ALL(Table1)) VAR CurrentVal_ = Table1[Value] RETURN CurrentVal_ - NextVal_Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Hi M-M-P ,
You could create a rank column to use EARLIER() function.
rank = RANKX ( 'Table', 'Table'[Timestamp],, ASC, DENSE )
Then create your "New Column".
New Column = VAR a = CALCULATE ( FIRSTNONBLANK ( 'Table'[Value], 1 ), FILTER ( 'Table', 'Table'[rank] = EARLIER ( 'Table'[rank] ) + 1 ) ) RETURN 'Table'[Value] - aHi,
This calculated column works
Data[Value]-LOOKUPVALUE(Data[Value],Data[Timestamp],CALCULATE(MIN(Data[Timestamp]),FILTER(Data,Data[Timestamp]>EARLIER(Data[Timestamp]))))
Hope this helps.
Hi AlB Ashish_Mathur
Thank you very much for your response.
Your solutions works. But unfortunately only if I filter my data hard in the Querie editor.
Unfortunately, I have simplified my example table too much.
I do not have unique Timestamps because there are several units in the same table that have values logged at the same time. See a more correct example below:
Next time I will remember to include all the important parameters :)
Hi,
Does this work?
Data[Value]-LOOKUPVALUE(Data[Value],Data[Timestamp],CALCULATE(MIN(Data[Timestamp]),FILTER(Data,Data[Timestamp]>EARLIER(Data[Timestamp])&&Data[UnitID]=EARLIER(Data[UnitID]))),Data[UnitID],Data[UnitID])
Hello,
I'm looking for help with a similar query where I want to subtract from a value three hours previous to the current row or 37 rows earlier rather than just the previous value. I'm very new to PowerBI so any help would be appreciated. Also for others reading the previous solution with as little knowledge as me, you need to change 'data' to your table name for it to work.
Thanks,
Eleanor
Hi,
Share some data to work with in a format (that can be pasted in an MS Excel file) and show the expected result.
