Creating an Updating Variable

[Anonymous]

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 

[v-yangliu-msft]

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.

[Greg_Deckler]

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

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]

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