Forum Discussion

M-M-P's avatar
M-M-P
Icon for Helper III rankHelper III
6 years ago
Solved

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 colum...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    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])
M-M-P's avatar
M-M-P
Icon for Helper III rankHelper III
6 years ago

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

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
6 years ago

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