Basis-value from a basis year - repeating using month only

Hi Anonymous ,

In your example data, amitchandak 's formula is able to return the correct result. So in your actual data, are you grouping by additional fields (i.e. fields other than Name, month, year)?

Also you can try to fill down using Power Query.
1. Add custom column.

2. Sort the table. Sort by month, Name, year in that order.

= Table.Sort(#"Added Custom",{{"month", Order.Ascending},{"Name", Order.Ascending},{"year", Order.Ascending}})

3. Select the custom column and fill down.

The complete code is as follows.

let
    Source = Excel.Workbook(File.Contents("\\xxxxxx\Data-example.xlsx"), null, true),
    Ark1_Sheet = Source{[Item="Ark1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Ark1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"year", Int64.Type}, {"month", Int64.Type}, {"Name", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "FillDown", each if [year] = 2018 then [Value] else null),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"month", Order.Ascending},{"Name", Order.Ascending},{"year", Order.Ascending}}),
    #"Filled Down" = Table.FillDown(#"Sorted Rows",{"FillDown"})
in
    #"Filled Down"

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.