问带组的电力查询的加权平均成本

EN

//rename fnRT_AP

(tbl as table)=>

let
    #"Add Running Total Column" = 
        Table.FromColumns(
            Table.ToColumns(tbl) &
            {List.Generate(
                ()=>[rt=tbl[Qty]{0}, idx=0],
                each [idx] < Table.RowCount(tbl),
                each [rt = [rt] + tbl[Qty]{[idx]+1}, idx=[idx]+1],
                each [rt])},
            type table[Date=date, Material=text, Type=text, In Price=Currency.Type, Qty=Int64.Type, Running Total=Int64.Type]),

    #"Add Avg Cost Column" = 
        Table.FromColumns(
            Table.ToColumns(#"Add Running Total Column") & 
            {List.Generate(
                ()=>[cst=if #"Add Running Total Column"[Type]{0}="In" then #"Add Running Total Column"[In Price]{0} else null, idx=0],
                each [idx] < Table.RowCount(#"Add Running Total Column"),
                each [cst=if #"Add Running Total Column"[Type]{[idx]+1}="Out" then [cst]
                    else ((if [cst]=null then 0 else [cst]) * #"Add Running Total Column"[Running Total]{[idx]} + 
                        #"Add Running Total Column"[In Price]{[idx]+1} * #"Add Running Total Column"[Qty]{[idx]+1})
                        / #"Add Running Total Column"[Running Total]{[idx]+1} , idx=[idx]+1],
                each [cst])},
        type table[Date=date, Material=text, Type=text, In Price=Currency.Type, Qty=Int64.Type, Running Total=Int64.Type, Avg Cost=Currency.Type])        
in
    #"Add Avg Cost Column"

let

//Change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table11"]}[Content],
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Material", type text}, {"Type", type text}, 
        {"In Price", Currency.Type}, {"Qty", Int64.Type}}),
    #"Capitalized Each Word" = Table.TransformColumns(#"Changed Type",{{"Type", Text.Proper, type text}}),

    #"Grouped Rows" = Table.Group(#"Capitalized Each Word", {"Material"}, {
        {"rtap", each fnRT_AP(_), 
            type table[Date=date, Material=text, Type=text, In Price=Currency.Type, Qty=Int64.Type, Running Total=Int64.Type, Avg Cost=Currency.Type]}
        }),

    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Material"}),
    #"Expanded rtap" = Table.ExpandTableColumn(#"Removed Columns", "rtap", {"Date", "Material", "Type", "In Price", "Qty", "Running Total", "Avg Cost"})
in
    #"Expanded rtap"

Qty running total in Date = 
CALCULATE(
    SUM('Stock'[Qty]),
    FILTER(
        ALLSELECTED('Stock'[Date]),
        ISONORAFTER('Stock'[Date], MAX('Stock'[Date]), DESC)
    )
)