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 b...
Need Help
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]
    )