Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🙂
Solved! Go to Solution.
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])
Hi,
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.
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.
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])
You are welcome.
Hi
I tried to use this calculation but got an error and that it expect the token Literal. Do you know why? The collum UnitID is changed with Device in my case, otherwise it's the same with value and timestamp
Hi,
My formula has to be written as a calculated column formula (not as a M language formula).
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] - a
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |