Power Platform Community Forum Thread Details

Help with a reset running total on two criteria

Hello,

New poster so go gentle with me!

My issue is fairly straight forward but I just don't seem to be able to solve it.

I have 3 columns of data:-

POSplitRows - This is a grouping of data lines based on other criteria not relevant to this issue.

Units - Values attributable to each "POSplitRows" line

Running Total - Running total of "Units" that I would like to reset when two criteria are reached 1. Reset when the "POSplitRows" grouping changes and 2. Reset within each "POSplitRows" grouping if the running total exceeds or equals 5000.

My solution

1. Extracted the columns needed from source data

2. Buffered "Units" & "POSplitRows" as lists

3. Created a custom function with the code below:-

= (values as list) as list =>
let
GRTList = List.Generate(
()=> [ GRT = values{0}, i = 0 ],
each [i] < List.Count(values),
each try
if [GRT] + values{[i]} <= 5000
then [GRT = [GRT] + values{[i] + 1}, i = [i] + 1]
else [GRT = values{[i] + 1}, i = [i] + 1]
otherwise [i = [i] + 1] ,
each [GRT] )
in
GRTList

4. Applied this function code to the data at stage 2 using the custom code below:-

= Table.FromColumns(
{
Source[POSplitRows], Source[Units],
fxRunningUnitGroupx(BufferedUnits, BufferedIndex)
},
{
"POSplitRows",
"Units",
"Running Total"
})

Problem

The split to reset at 5000 units seems to be working as expected (Yellow box below) but the reset based on the "POSplitRows" grouping is performed on the second line of the new "POSplitRows" grouping (Red Box below). I want the "Running Total" column to show the first line of the new grouping as the new running total (ie Start at 404 units, not 287)