need to fill gaps based on past cost

[rwamorim]

I need to create a graph of the cost of my inventory based on item movement, however, there are items that are not moved in the month, so I need a formula that does that. The idea is to put this on a line graph that will show if my inventory cost is growing or not.

Example:
I had movement of item x in March/21 leaving my inventory at 500. The other movement was in July/21 which left my inventory at 100. From April/21 to June/21 the function has to copy these 500 so that I don't have gaps.

In the table I have the columns: competence, product code, product, unit, stock and the cost of stock in the competition.

unfortunately I don't know how to do this :~(

I'm sharing the database looking for help.

https://docs.google.com/spreadsheets/d/1FI21etPCE1guDCjHPxRzlTxxVS6Z4m2m/edit?usp=sharing&ouid=10382... 

[v-stephen-msft · Accepted solution]

Hi @rwamorim ，

Need to transform table in powerquery to determine the conditions to filter out the day of the month. It will automatically fill the month where the stock field has not changed. Finally the results are presented in powerbi desktop, refer to the following.

let
    Source = Excel.Workbook(File.Contents("need to fill gaps based on past cost.xlsx"), null, true),
    Planilha1_Sheet = Source{[Item="Planilha1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Planilha1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"competence", type date}, {"product code", Int64.Type}, {"product", type text}, {"unit", type text}, {"stock", Int64.Type}, {"cost of stock", type number}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"competence", Order.Ascending}}),
    #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([competence] <> null)),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Date", each { Number.From([competence])..Number.From([competence]) }),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Date",{{"Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"product code", "product", "unit", "cost of stock", "Date"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "Custom", each Table.Max(Planilha1,"competence")),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom1", "Custom", {"competence"}, {"Custom.competence"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Custom",{{"Custom.competence", "Max_date"}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Renamed Columns",{{"Max_date", type date}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Custom", each {Number.From([competence])..Number.From([Max_date])}),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom2", "Custom"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Expanded Custom1",{{"Custom", type date}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type3", "Custom.1", each if Date.Day([Custom])=1 then 1 else 0),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom3", each ([Custom.1] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Max_date", "Custom.1"})
in
    #"Removed Columns1"

If the problem is still not resolved, please point it out. Looking forward to your feedback.

Best Regards,
Stephen

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