Forum Discussion

Fraze's avatar
Fraze
Helper I
4 years ago
Solved

Using previous row in current row calculation

Hello, trying to create an outstanding column using data from the previous row.   See example below for situation.   ID #1 has no previous data, so the outstanding balance is the Net change of $1...
Need Help
Tips and Tricks
  • smpa01's avatar
    smpa01
    4 years ago

    Fraze  can you try this

    Column = SUMX (
    FILTER (
        'Table',
        'Table'[Date] <= EARLIER ( 'Table'[Date] )
            && 'Table'[Customer] = EARLIER ( 'Table'[Customer] )
    ),
    'Table'[Net Change]
)

     

     

smpa01's avatar
smpa01
Community Champion
4 years ago

Fraze  can you try this

Column = SUMX (
    FILTER (
        'Table',
        'Table'[Date] <= EARLIER ( 'Table'[Date] )
            && 'Table'[Customer] = EARLIER ( 'Table'[Customer] )
    ),
    'Table'[Net Change]
)

 

 

smpa01's avatar
smpa01
Community Champion
4 years ago

AlexisOlson  on the above, I have noticed that. if the TransactionID is text, the running total goes sideways, if my partition is on ID

 

 

However, it behaves properly I change that back to integer.

 

 

I thought DAx stores the text values as per the CHAR value

 

 

 

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    I'm not sure what you mean by "stores the text values as per the CHAR value" but the problem is that "1", "10", and "3" are all <= "3" sorted alphabetically.

    • smpa01's avatar
      smpa01
      Community Champion
      4 years ago

      AlexisOlson got it !!! many Thanks !!!!