cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Conditional incremental subtraction from an original number?

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

4 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft

## Re: Conditional incremental subtraction from an original number?

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

Super User

## Re: Conditional incremental subtraction from an original number?

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}}),
"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"```

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

Super User

## Re: Conditional incremental subtraction from an original number?

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 ] )```

Try my new Power BI game Cross the River
Frequent Visitor

## Re: Conditional incremental subtraction from an original number?

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

Jason

4 REPLIES 4
Microsoft

## Re: Conditional incremental subtraction from an original number?

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

Super User

## Re: Conditional incremental subtraction from an original number?

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}}),
"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"```

If you liked my solution, please give it a thumbs up. An if I did answer your question, please mark my post as a solution. Thanks!

Proud to be a Datanaut!

Imke Feldmann

Super User

## Re: Conditional incremental subtraction from an original number?

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 ] )```

Try my new Power BI game Cross the River
Frequent Visitor

## Re: Conditional incremental subtraction from an original number?

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

Jason

Announcements

#### New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

#### Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

#### Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)