Generate table for each month with accumulative values having the year

Hi Borja204 ,

What I did was to create a new table with the years / months:

Added a new column to this table with the name of the table with the sales objectives:

SalesObjectives

Expanded the new column

Then added two custom columns:

[Custom.SalesObjective]/12*[Month]

[Custom.ClientObjective]/12*[Month]

Deleted the previous two columns and final result below:

Check full code for the second table below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Vc6pEQAwCATAXk4jgPy1MPTfRqIyh1y3EXB1hcCQ8uGMxuiMwZiMxdiMwzAtKgd7ibw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Year = _t, Month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Month", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each SalesObjectives),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Country", "TypeID", "Year", "SalesObjective", "ClientObjective"}, {"Custom.Country", "Custom.TypeID", "Custom.Year", "Custom.SalesObjective", "Custom.ClientObjective"}),
    #"Added Custom1" = Table.AddColumn(#"Expanded Custom", "Salesobjective", each [Custom.SalesObjective]/12*[Month]),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ClientObjective", each [Custom.ClientObjective]/12*[Month]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom.SalesObjective", "Custom.ClientObjective"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Custom.Country", Order.Ascending}, {"Year", Order.Ascending}, {"Month", Order.Ascending}})
in
    #"Sorted Rows"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcg1W0lEyAmEDIwMgZQhmmyjF6kQrhXoD2cYIOWMziAKwXDCqHFCLjpI5UC4WAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CountryId = _t, TypeId = _t, Year = _t, SalesObjective = _t, ClientObjective = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CountryId", type text}, {"TypeId", Int64.Type}, {"Year", Int64.Type}, {"SalesObjective", Int64.Type}, {"ClientObjective", Int64.Type}}),
    #"Added Custom" = Table.RemoveColumns(Table.AddColumn(#"Changed Type", "Custom", each Table.FromRows(List.Accumulate({1..12}, {}, (s,c) => s & {{c, c/12*[SalesObjective], c/12*[ClientObjective]}}), {"Month", "SalesObjective", "ClientObjective"})), {"SalesObjective", "ClientObjective"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Month", "SalesObjective", "ClientObjective"}, {"Month", "SalesObjective", "ClientObjective"})
in
    #"Expanded Custom"