Running total by group with a reset

[Anonymous]

Either in a Dax calculated column or in M/Power Query I need to create a running total that groups by a part number and resets if it goes below 0. 

This is essentially projecting closing month inventory for each part we have for the next year. 

The dataset is a single grouped table that gives a net quantity for each month (i.e. includes the sales forecast and any new supply coming in that month). 

As you can see from the table below I need a running total for each part that once it detects the previous month was below 0 then it takes the net quantity for the current month - see expected outcome column. 

Date	Part	Net Quantity	Closing Inventory (Expected Outcome)
30/06/2021	A	1000	1000
31/07/2021	A	-150	850
31/08/2021	A	-200	650
30/09/2021	A	-200	450
31/10/2021	A	-200	250
30/11/2021	A	-200	50
31/12/2021	A	50	100
31/01/2022	A	-200	-100
28/02/2022	A	-200	-200
31/03/2022	A	-200	-200
30/04/2022	A	50	50
31/05/2022	A	-200	-150
30/06/2022	A	100	100
30/06/2021	B	100	100
31/07/2021	B	20	120
31/08/2021	B	20	140
30/09/2021	B	-30	110
31/10/2021	B	-35	75
30/11/2021	B	25	100
31/12/2021	B	-50	50
31/01/2022	B	-50	0
28/02/2022	B	50	50
31/03/2022	B	-50	0
30/04/2022	B	-100	-100
31/05/2022	B	-50	-50
30/06/2022	B	100	100

Thank you! 

[v-yalanwu-msft]

Hi, @Anonymous ;

You could use List accumulate() in power query, as follows:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZBLEoQgDAXvwlorHwyjS72G5f2vIWRqah4aFmzoahL6PFNm4kLKKmlKez3CzOmaKhHiD5JZ7E/WjujPqa9tMRESHjkiQ0eRwHw39GnoSqwhaU6OSd15QQJTbGgUJPIEbeEDAbZsQIOSeI8d2/2cg4xfYO+K/pK9G7oQRUTQNTz6HjkSuoAO8Ns2UgoCN64b", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Part = _t, #"Net Quantity" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Part", type text}, {"Net Quantity", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Part", Order.Ascending}, {"Date", Order.Ascending}}),
    Custom1 = Table.Group(#"Sorted Rows", {"Part"}, {{"A", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    A = Table.ExpandTableColumn(Custom1, "A", {"Date", "Net Quantity", "Index"}, {"Date", "Net Quantity", "Index"}),

    FX = (values as list, grouping as list) as list =>

let
    GRTList = List.Generate
    ( 
        ()=> [ GRT = values{0}, i = 0 ],

        each [i] < List.Count(values),

        each try 
                 if grouping{[i]} = grouping{[i] + 1}  
                 then if [GRT]>0  then  [GRT = [GRT] + values{[i] + 1}, i = [i] + 1] else [GRT =  values{[i] + 1}, i = [i] + 1] 
                 else [GRT = values{[i] + 1}, i = [i] + 1]
        
             otherwise [i = [i] + 1]
    ,
        each [GRT]
    )
in
    GRTList,

    BufferedValues = List.Buffer(A[Net Quantity]),
    Bufferedgroup = List.Buffer(A[Part]),
    Custom2 = Table.FromColumns(
    {
      Source[Part], A[Index], Source[Net Quantity], Source[Date],
     FX(BufferedValues, Bufferedgroup)
    },
    {
      "Part",
      "Index",
      "Net Quantity","Date",
      
      "Running Total"
    })
in
    Custom2

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you! This works expectly as I'd expect it to and calculates quicly as well! 😁

One thing for anyone else reading this as a solution, the code above will only work if the data is already sorted. I had to change my data source (SQL) slightly so that it was ordered by part & date. I'm not 100% sure why has there is a sort in the code above. 

@v-yalanwu-msft if you have the time it would be great to explain the logic of the code. I'm not overly familiar with M. I have marked as accepted solution. 

[FrankAT · Accepted solution]

Hi @Anonymous ,

you can do it like this:

Closing Inventory = 
CALCULATE (
    SUM ( 'Table'[Net Quantity] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Part] ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

This is just a grouped running total, unfortunately it does not take into account the reset so I cannot accept it as the solution. 

[Jihwan_Kim]

Link to the PBIX file