Power Query / Power Pivot - stock calculation

Hi nexami 

Please see the below output based on the original data sample provided.

Please see the below M expression I used to Pivot the data on transaction column and later creatred a running total for Purchase - sales to be deducted of the stock value for every line. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnQytTBX0lEyMjC0BFKGRkAiJD8XSAaUFiVnJBangiUNlGJ18CkOTswBKTQ0xarOGJuhZtjNNMYwk7DlJfnJ2SAhMxxKTbDZb0pILdR+E5DCWAA=", 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),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom", each List.Sum(
            Table.AddColumn( 
                Table.SelectRows( 
                    #"Added Custom1", let _cmt = [CMT], _earWeek = [YearWeek] in each [CMT] = _cmt and [YearWeek] <= _earWeek 
                ),
                "var", each [Sale]- [Purchase]
            )[var]
        )),
    #"Replaced Value" = Table.ReplaceValue(#"Added Custom2", 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"

Sales and purchase will be normal sum aggregation where Last Stock is as below.

Last Stock = 
VAR _maxMonth = MAX( test[Week] )
VAR _maxYear = MAX( test[Year] )
RETURN 
CALCULATE(
    SUM( test[Stock] ),
    test[Week] = _maxMonth && 
    test[Year] = _maxYear
)

Due to complicated M expression and the size of your data this will perform rather slow.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.