Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Creating an Updating Variable

Hello, 

I am trying to create a column that subtractrs from the previous calculated number (similar to an updating variable). I am getting confused on the DAX code.

 

Currently I have tried this but it did not work 

 

Improvements = 

VAR Total = Sum[table]

VAR NewTotal = Total - [Current Improvements]

 

Then I would like to update my total variable 

 

Total = NewTotal

Return 

Total 

 

 

 

 

Need Help

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    Anonymous Nope, VAR's are not variable, they are static. I know, confusing. You can have nested VAR's that are the same name so like:

    Improvements = 

VAR Total = Sum[table]

VAR NewTotal = Total - [Current Improvements]
RETURN
  VAR Total = NewTotal
  RETURN Total

    Sorry, probably confused you more.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi, 

      I have tried this DAX instead, however, I keep getting an error with my index column I created in queary editory. 

       

       

      Improvements =

      DDBB[Total Column]
          - SUMX (
              FILTER (
                  DDBB,
                 DDBB[Index]< EARLIER(DDBB[Index])
              ),
              DDBB[Current Improvement1]
          )
       
       
       
      Thanks!

       

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        3 years ago

        Anonymous Try:

        Improvements =

SUM(DDBB[Total RPN Column])
    - SUMX (
        FILTER (
            DDBB,
           DDBB[Index]< EARLIER(DDBB[Index])
        ),
        DDBB[Current Improvement1]
    )

or maybe:

Improvements =
VAR __Index = MAX(DDBB[Index])
RETURN
SUM(DDBB[Total RPN Column])
    - SUMX (
        FILTER (
           ALL(DDBB),
           DDBB[Index]< __Index)
        ),
        DDBB[Current Improvement1]
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi  Anonymous ,

     

    You can use a Var to represent the result is the same:

    Improvements = 
VAR Total = Sum[table] - [Current Improvements]
Return 
Total

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.