Forum Discussion

Fraze's avatar
Fraze
Icon for Helper I rankHelper 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
Icon for Community Champion rankCommunity Champion
4 years ago

Fraze  you can create a measure like this

Outstanding =
VAR _filter =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Transaction ID] <= MAX ( 'Table'[Transaction ID] )
            && 'Table'[Customer] = MAX ( 'Table'[Customer] )
    )
RETURN
    CALCULATE ( SUM ( 'Table'[NetChange] ), _filter )

 

or a calculated column like this

Column =
SUMX (
    FILTER (
        'Table',
        'Table'[Transaction ID] <= EARLIER ( 'Table'[Transaction ID] )
            && 'Table'[Customer] = EARLIER ( 'Table'[Customer] )
    ),
    'Table'[NetChange]
)
  • Fraze's avatar
    Fraze
    Icon for Helper I rankHelper I
    4 years ago

    I tried this approach, but row 2 jumped 35k rather than 20k

     

     

    • smpa01's avatar
      smpa01
      Icon for Community Champion rankCommunity 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
        Icon for Community Champion rankCommunity 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