Power Query / Power Pivot - stock calculation

If you're worried about performance, you can use this trick to substantially improve speed for a case like this: https://www.thebiccountant.com/2017/05/29/performance-tip-partition-tables-crossjoins-possible-powerquery-powerbi/

Your code would look like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQytTBX0lEyMjC0BFKGRkAiJD8XSAaUFiVnJBangiUNlGJ18CkOTswBKTQ0xarOGJuhZtjNNMYwk7DlJfnJ2SAhMxxKTbDZb0pILdR+EySFFkSEFCHV8KDCrhBHWBFSjAgsgvYjhRZ2tTiCi5BiRHgBVcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CMT = _t, Year = _t, Week = _t, Customer = _t, Transaction = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CMT", type text}, {"Year", Int64.Type}, {"Week", Int64.Type}, {"Customer", type text}, {"Transaction", type text}, {"Value", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Transaction]), "Transaction", "Value"),
    #"Added Custom1" = Table.AddColumn(#"Pivoted Column", "YearWeek", each [Year] * 100 + [Week], Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Custom1", {"CMT"}, {{"All", (Partition) => Table.AddColumn(Partition, "Custom", each List.Sum(
            Table.AddColumn( 
                Table.SelectRows( 
                    Partition, let  _earWeek = [YearWeek] in each [YearWeek] <= _earWeek 
                ),
                "var", each [Sale]- [Purchase]
            )[var]
        )) }}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Year", "Week", "Customer", "Purchase", "Sale", "Stock", "YearWeek", "Custom"}, {"Year", "Week", "Customer", "Purchase", "Sale", "Stock", "YearWeek", "Custom"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded All", each [Stock], each [Stock] + [Custom],Replacer.ReplaceValue,{"Stock"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Stock"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Filled Down",{{"Stock", Int64.Type}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type1",each [Stock], each [Stock] - [Custom],Replacer.ReplaceValue,{"Stock"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value1",{{"Stock", Int64.Type}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type2",{"CMT", "Year", "Week", "Customer", "Purchase", "Sale", "Stock"})
in
    #"Removed Other Columns"

You'll find some more performance tricks here: https://www.thebiccountant.com/speedperformance-aspects/ 

Hi nexami ,

if you want to learn how to integrate M code into your own solution, this video might help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-code-into-your-existing/m-p/179314