Dynamic Measure to Carry Over Ending Balance to become Beginning Balance of Next Month

Hi amitchandak , Ritaf1983 , do you have time to take a look at this? Thanks!

Hi Mugsy ,

Below is my table:

The following M code will help you:

let 

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUQIiQyOlWJ1oJUMQ0xgkBOIZgXiGMB5I2NAExgMxDE3BvFgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Index = _t, Import = _t, Value = _t]), 

    ChangedType = Table.TransformColumnTypes(Source,{{"Index", Int64.Type}, {"Import", Int64.Type}, {"Value", Int64.Type}}), 

    InitialEnding = 9800, 
    InitialBegin = 10000,

    AccumulateColumns = List.Accumulate( 

        List.Zip({ChangedType[Index], ChangedType[Import]}), 

        {[Index=0, Begin=InitialBegin, Export=null, Ending=InitialEnding]}, 

        (state, current) => 

        let 

            CurrentIndex = current{0}, 

            CurrentImport = current{1}, 

            PreviousEnding = if CurrentIndex = 0 then InitialBegin else state{CurrentIndex}[Ending], 

            CurrentExport = if CurrentIndex = 0 then null else (PreviousEnding + CurrentImport) / 2, 

            CurrentEnding = if CurrentIndex = 0 then InitialEnding else PreviousEnding + CurrentImport - CurrentExport, 

            CurrentRecord = [Index=CurrentIndex, Begin=PreviousEnding, Export=CurrentExport, Ending= CurrentEnding] 

        in 

            state & {CurrentRecord} 

    ), 

    AccumulatedTable = Table.FromRecords(AccumulateColumns), 

    JoinedTable = Table.NestedJoin(ChangedType,{"Index"},AccumulatedTable,{"Index"},"NewColumns",JoinKind.LeftOuter), 

    ExpandedTable = Table.ExpandTableColumn(JoinedTable, "NewColumns", {"Begin",  "Ending"}), 

    FinalTable = ExpandedTable, 

    #"Removed Top Rows" = Table.Skip(FinalTable,1) 

in 

    #"Removed Top Rows"

The final output is shown in the following figure:

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.