Dynamic Allocation % Table

Unfortunately it still get stuck 😞

I have solved it by replicating the souce sales dataset by each report period (e.g. 31/12/19, 31/01/2020). Marking it with a specific column (report date) and then grouping by this new column and by BU to find out sales by bu YTD

Thanks

Maturin

Hey Anonymous - glad you found something that worked. I gave it one more try and this groups at a much much higher level. Worth a shot if you are not entirely happy with your existing solution. Returns the same results.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZIxDsMgDEXvwpwI2xgDa7ceoFOUsWO33l/FDIiQBLp9rMe3v+VtM4/XimYxDi2gJSDIj+f3/VkpCwSzL5WhZIEODHJWCSeQOolvIe3mzk6xOtElpIxIzxzHxqjlNIFUMbTQee6iJIwhTSM8YXQkjuNsykSe52c/ZorydUnuPpqHFrpYkn7lsVGJFt3YKGizOGZEq75nuhvRiVL6Awr5kPYf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"BU Name" = _t, Date = _t, Item = _t, #"Sales €" = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-BM"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Sales €", Int64.Type}}),
    #"Grouped Rows" = 
        Table.Group(
            #"Changed Type",
            {"BU Name"}, 
            {
                {
                    "All Rows",
                    each
                        let
                            varTable = _
                        in
                        Table.AddColumn(
                            _,
                            "YTD Revenue",
                            each
                                let 
                                    varCurrentMonthEnd = Date.EndOfMonth([Date]),
                                    varCurrentYear = Date.Year([Date])
                                in
                                List.Sum(
                                    Table.SelectRows(
                                        varTable,
                                        each Date.Year([Date]) = varCurrentYear and [Date] <= varCurrentMonthEnd
                                    )[#"Sales €"]
                                )
                        ), 
                    type table [BU Name=nullable text, Date=nullable date, Item=nullable text, #"Sales €"=nullable number, YTD Revenue = nullable number]
                    }
                }
            ),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Date", "Item", "YTD Revenue"}, {"Date", "Item", "YTD Revenue"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded All Rows",{"BU Name", "Date", "YTD Revenue"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    MonthlyTotal = 
        Table.Group(
            #"Removed Duplicates", 
            {"Date"},
            {
                {"YTD Total", each List.Sum([YTD Revenue]), type nullable number}
            }
        ),
    #"Grouped Rows1" = Table.Group(#"Removed Duplicates", {"Date"}, {{"All Rows", each _, type table [BU Name=nullable text, Date=nullable date, YTD Revenue=nullable number]}}),
    AddYTDTotal = 
        Table.AddColumn(
            #"Grouped Rows1",
            "YTD Total",
            each
                let
                    varCurrentDate = [Date]
                in
                Table.SelectRows(
                    MonthlyTotal, 
                    each [Date] = varCurrentDate
                    )[YTD Total]{0}
        ),
    #"Expanded All Rows1" = Table.ExpandTableColumn(AddYTDTotal, "All Rows", {"BU Name", "YTD Revenue"}, {"BU Name", "YTD Revenue"}),
    #"Inserted Division" = Table.AddColumn(#"Expanded All Rows1", "Division", each [YTD Revenue] / [YTD Total], Percentage.Type),
    #"Removed Other Columns1" = Table.SelectColumns(#"Inserted Division",{"Date", "BU Name", "Division"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Other Columns1", List.Distinct(#"Removed Other Columns1"[#"BU Name"]), "BU Name", "Division", List.Sum)
in
    #"Pivoted Column"