Store product of previous row subtracting from another column into the next row

[Anonymous]

CntrlRunTot is a hardcoded column of values. I'm trying to make a calculated column that subtracts its previous row from the demand qty that corresponds to its row and have that product stored in the row below. Ex. 188535 - 2976 = 185559 which is then stored a row beneath the 188535 row. Right now my Running Total calculated column depends on every value of CntrlRunTot but I need it to just depend on that first number 188535 and then be able to subtract Demand Qty and store the following values within its consecutive rows.

Current Setup

[johncolley]

Hi @Anonymous ,

I believe the 'Value from previous row' section from https://exceltown.com/en/tutorials/power-bi/powerbi-com-and-power-bi-desktop/power-bi-data-sources/power-query-get-value-from-previous-row/ addresses your problem.

For your example:

1. Create a second index column (Index.2) with 1 subtracted from it 

2. Merge the table with itself, joining on Index and Index.2

3. Expand the table pulling in the new Demand Qty column 

4. Create new column calculated or custom that subtracts the new Dmand Qty from Cntrlruntot

Small example result below:

[Anonymous]

I need the Custom column to only rely on the first value of the CntrlRunTot column. I need the difference of (in the example photo attached) "x - z" to be displayed in the same column as "x", so the following row where value "y" is located. Right now Custom is still depending on all of the values in CntrlRunTot.

[johncolley]

Hi @Anonymous ,

Try using List.Max(#"Changed Type"[CntrlRunTot]) - [Subtracted from Column.Demand Qty]

[Anonymous]

Do I add the List.Max function into the custom column like this or? (if so it gives me an error)

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can achieve it by DAX or Power Query method, please see the details in the attachment.

Power Query method:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcjBDQAhCAXRXv6ZgwIi1kLsv411ZRPdwySTFwEQGptoWVO/MCngquN9At/c2T1ZVmW3Xaqk689tdEtvx+cD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Demand Qty" = _t, CntrlRunTot = _t, Index = _t, FindNegVals = _t, NegVals = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Demand Qty", Int64.Type}, {"CntrlRunTot", Int64.Type}, {"Index", Int64.Type}, {"FindNegVals", Int64.Type}, {"NegVals", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "NextRowDQTY", each (try  #"Changed Type" [Demand Qty] { [Index]   } otherwise null)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Running Total", each List.Max(#"Added Custom"[CntrlRunTot])-List.Sum(List.FirstN(#"Added Custom"[NextRowDQTY],[Index]))),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"NextRowDQTY"})
in
    #"Removed Columns"

Max of Column in Power Query

Refer to Previous Row and Next Row in Power Query

DAX method:

1. Create a calculatd column to get the next row value

Nextrow Value = 
CALCULATE (
    MAX ( 'Table'[Demand Qty] ),
    FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) + 1 )
)

2. Create a calculated column as below to get the running total values

Col_Running Total = 
CALCULATE ( MAX ( 'Table'[CntrlRunTot] ), ALL ( 'Table' ) )
    - CALCULATE (
        SUM ( 'Table'[Nextrow Value] ),
        FILTER ( 'Table', 'Table'[Index] <= EARLIER ( 'Table'[Index] ) )
    )

Power BI: Get value from next row

Best Regards