Forum Discussion

M-M-P's avatar
M-M-P
Helper 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])
AlB's avatar
AlB
Community Champion
6 years ago

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

  • M-M-P's avatar
    M-M-P
    Helper 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
      Community 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