Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
M-M-P
Helper III
Helper III

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:

Tabel 1.png

 

I'm trying to subtract the current value from the previous value so I get a new column that looks like this:

Tabel 2.png

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 🙂

1 ACCEPTED 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])

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

13 REPLIES 13
Ashish_Mathur
Super User
Super User

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

eleanoracook_0-1670419018413.png

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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:

Tabel 3.png

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])

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 
Thanks a lot it works 🙂

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

@Ashish_Mathur 

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

Sofus_1-1617821432777.png

 

 

Hi,

My formula has to be written as a calculated column formula (not as a M language formula).  


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-eachen-msft
Community Support
Community Support

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
AlB
Super User
Super User

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  Datanaut

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  Datanaut

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.