Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Substract from previous row and keep it in same column

 

Hi I have question how to substract from previous row and input it in current row and for the 1st row we have 1,000,000 as starting value

 

Thank

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    You can create two measures as below to achieve it:

     

    FORECAST REDUCER = SUM('Table'[PRODUCTIVITY])*SUM('Table'[PEOPLE])*30
    FORECAST = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[DATE],
        "Forecast",
            1000000
                - SUMX (
                    FILTER ( ALL ( 'Table' ), 'Table'[DATE] <= SELECTEDVALUE ( 'Table'[DATE] ) ),
                    [FORECAST REDUCER]
                )
    )
RETURN
    SUMX ( _tab, [Forecast] )

     

    Best Regards

2 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    Use the List.Accumulate function.  You need some sort of an index column that makes each row unique and sortable.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

    You can create two measures as below to achieve it:

     

    FORECAST REDUCER = SUM('Table'[PRODUCTIVITY])*SUM('Table'[PEOPLE])*30
    FORECAST = 
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[DATE],
        "Forecast",
            1000000
                - SUMX (
                    FILTER ( ALL ( 'Table' ), 'Table'[DATE] <= SELECTEDVALUE ( 'Table'[DATE] ) ),
                    [FORECAST REDUCER]
                )
    )
RETURN
    SUMX ( _tab, [Forecast] )

     

    Best Regards