Conditional incremental subtraction from an original number?

[fendjas]

Hi all,

Here is my data

I want to be able to subtract the first two columns, but then take that number and create a new column that stores that value in the next index - so like an incremental subtraction from the original value. However if the subtraction yields a negative number, it just defaults to 0. 

The idea is that when it gets to a new StockCode, it will repeat the process. How can this be done?

 Jas

[v-huizhn-msft · Accepted solution]

Hi @fendjas,

Please follow the steps below and you will get expected result.

1. Add an index column by add index column in Power Query Editor, please review more details here.

2. Create a calculated column [column] using the formula.

Column =
VAR Y =
    MAXX ( ALLEXCEPT ( Test, Test[Index] ), Test[Index] )
RETURN
    CALCULATE ( SUM ( Test[PastDue+MRP] ), FILTER ( Test, Test[Index] <= Y ) )

3. Create [column 2] using the formula.

Column 2 = MAXX(FILTER(Test,Test[StockCode]=EARLIER(Test[StockCode])),Test[FG])

4. Create [column 3] using the formula.

Column 3 =
VAR Y =
    MAXX ( ALLEXCEPT ( Test, Test[Index] ), Test[Index] )
RETURN
    Test[Column]
        - MAXX (
            FILTER ( Test, Test[StockCode] = EARLIER ( Test[StockCode] ) ),
            IF (
                Test[FG] <> 0,
                LOOKUPVALUE ( Test[Column], Test[Index], Test[Index] - 1 ),
                0
            )
        )

5. Create another calculated column to get right result based on columns above.

FGBal = IF((Test[Column 2]-Test[Column 3])>0,(Test[Column 2]-Test[Column 3]),0)

Please review result as follows.

And you can download the .pbix file for more details.

Best Regards,
Angelia

[ImkeF · Accepted solution]

Please paste this code into the advanced editor and follow the steps. Then replace the code in step "Source" by a reference to your table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSpgZKsToQARDXDJVrAuE6AZlGYOWGRggREN8YlWuBygVpjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StockCode = _t, FG = _t, #"PastDue+MRP" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StockCode", type text}, {"FG", Int64.Type}, {"PastDue+MRP", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"StockCode"}, {{"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn([All], 
                                                                                      "FGBal", 
                                                                                       (x) => List.Max({0, 
                                                                                                        if x[FG] = 0 
                                                                                                            then [All]{0}[FG] - List.Sum(Table.SelectRows([All], 
                                                                                                                                                          (y) => y[Index]<=x[Index])[#"PastDue+MRP"]) 
                                                                                                            else x[FG]-x[#"PastDue+MRP"]
                                                                                                        })
                                                                                        )
                                     ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"FG", "PastDue+MRP", "FGBal"}, {"FG", "PastDue+MRP", "FGBal"})
in
    #"Expanded Custom"

[Zubair_Muhammad · Accepted solution]

Hi @fendjas

This could just be another way of doing it

After adding the Index Column from Query Editor...add this calculated column

Column =
VAR Cumulative =
    SUMX (
        FILTER (
            Table1,
            [Index] <= EARLIER ( [Index] )
                && [Stock Code] = EARLIER ( [Stock Code] )
        ),
        [ FG ] - [ MRPQty ]
    )
VAR PreviousCumulative =
    SUMX (
        FILTER (
            Table1,
            [Index] < EARLIER ( [Index] )
                && [Stock Code] = EARLIER ( [Stock Code] )
        ),
        [ FG ] - [ MRPQty ]
    )
RETURN
    IF ( Cumulative < 0, 0, PreviousCumulative + [ FG ] - [ MRPQty ] )

[fendjas · Accepted solution]

Thanks all! These solutions all worked. I appreciate the help.

Jason

[ImkeF · Accepted solution]

Please paste this code into the advanced editor and follow the steps. Then replace the code in step "Source" by a reference to your table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSpgZKsToQARDXDJVrAuE6AZlGYOWGRggREN8YlWuBygVpjwUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [StockCode = _t, FG = _t, #"PastDue+MRP" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StockCode", type text}, {"FG", Int64.Type}, {"PastDue+MRP", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1),
    #"Grouped Rows" = Table.Group(#"Added Index", {"StockCode"}, {{"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddColumn([All], 
                                                                                      "FGBal", 
                                                                                       (x) => List.Max({0, 
                                                                                                        if x[FG] = 0 
                                                                                                            then [All]{0}[FG] - List.Sum(Table.SelectRows([All], 
                                                                                                                                                          (y) => y[Index]<=x[Index])[#"PastDue+MRP"]) 
                                                                                                            else x[FG]-x[#"PastDue+MRP"]
                                                                                                        })
                                                                                        )
                                     ),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"FG", "PastDue+MRP", "FGBal"}, {"FG", "PastDue+MRP", "FGBal"})
in
    #"Expanded Custom"

[Zubair_Muhammad · Accepted solution]

Hi @fendjas

This could just be another way of doing it

After adding the Index Column from Query Editor...add this calculated column

Column =
VAR Cumulative =
    SUMX (
        FILTER (
            Table1,
            [Index] <= EARLIER ( [Index] )
                && [Stock Code] = EARLIER ( [Stock Code] )
        ),
        [ FG ] - [ MRPQty ]
    )
VAR PreviousCumulative =
    SUMX (
        FILTER (
            Table1,
            [Index] < EARLIER ( [Index] )
                && [Stock Code] = EARLIER ( [Stock Code] )
        ),
        [ FG ] - [ MRPQty ]
    )
RETURN
    IF ( Cumulative < 0, 0, PreviousCumulative + [ FG ] - [ MRPQty ] )

[v-huizhn-msft · Accepted solution]

Hi @fendjas,

Please follow the steps below and you will get expected result.

1. Add an index column by add index column in Power Query Editor, please review more details here.

2. Create a calculated column [column] using the formula.

Column =
VAR Y =
    MAXX ( ALLEXCEPT ( Test, Test[Index] ), Test[Index] )
RETURN
    CALCULATE ( SUM ( Test[PastDue+MRP] ), FILTER ( Test, Test[Index] <= Y ) )

3. Create [column 2] using the formula.

Column 2 = MAXX(FILTER(Test,Test[StockCode]=EARLIER(Test[StockCode])),Test[FG])

4. Create [column 3] using the formula.

Column 3 =
VAR Y =
    MAXX ( ALLEXCEPT ( Test, Test[Index] ), Test[Index] )
RETURN
    Test[Column]
        - MAXX (
            FILTER ( Test, Test[StockCode] = EARLIER ( Test[StockCode] ) ),
            IF (
                Test[FG] <> 0,
                LOOKUPVALUE ( Test[Column], Test[Index], Test[Index] - 1 ),
                0
            )
        )

5. Create another calculated column to get right result based on columns above.

FGBal = IF((Test[Column 2]-Test[Column 3])>0,(Test[Column 2]-Test[Column 3]),0)

Please review result as follows.

And you can download the .pbix file for more details.

Best Regards,
Angelia