Sum based on Parameter in Power Query

[MarkDGaal]

Hi guys, i'm taking inflation data from statbureau.org/ and attempting to creating a table in PQ that will show me the cumulative inflation % from a given point.

For example, if since 2013 inflation has been 1% per year. Then the final table would show:

Year | Cumulative Inflation %
2013 | 3.75%
2014 | 2.75%
2015 | 1.75%
2016 | .75% (because we are 9/12th of the way through the year)

Here is my query thus far, you can ignore all the code shifting Year to FY (that is me just aligning the sums to the fiscal year of my organization) 

let
    Source = Web.Page(Web.Contents("https://www.statbureau.org/en/united-states/inflation-tables", [Timeout=#duration(0, 0, 1, 0)])),
    Data0 = Source{0}[Data],
    #"Changed Type3" = Table.TransformColumnTypes(Data0,{{"Year", Int64.Type}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}, {"Apr", type number}, {"May", type number}, {"Jun", type number}, {"Jul", type number}, {"Aug", type number}, {"Sep", type number}, {"Oct", type number}, {"Nov", type number}, {"Dec", type number}, {"Total", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type3", {"Year"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] <> "Total")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Month"}, {"Value", "Inflation Percent"}}),
    #"CC: Month #" = Table.AddColumn(#"Renamed Columns", "Month #", each if [Month] = "Jan" then "1" else if [Month] = "Feb" then "2" else if [Month] = "Mar" then "3" else if [Month] = "Apr" then "4" else if [Month] = "May" then "5" else if [Month] = "Jun" then "6" else if [Month] = "Jul" then "7" else if [Month] = "Aug" then "8" else if [Month] = "Sep" then "9" else if [Month] = "Oct" then "10" else if [Month] = "Nov" then "11" else if [Month] = "Dec" then "12" else "Not Possible" ),
    #"Changed Type" = Table.TransformColumnTypes(#"CC: Month #",{{"Inflation Percent", type number}, {"Month #", Int64.Type}, {"Year", Int64.Type}}),
    #"CC: Fiscal Year" = Table.AddColumn(#"Changed Type", "FY", each if([#"Month #"]=10 or [#"Month #"]= 11 or [#"Month #"] = 12)
then [Year]+1

else if([#"Month #"] <= 9)
then [Year]

else "Not Possible"),
    #"Added Custom" = Table.AddColumn(#"CC: Fiscal Year", "Fiscal Month", each if([#"Month #"]=10)
then 1

else if([#"Month #"]=11)
then 2

else if([#"Month #"]=12)
then 3

else if([#"Month #"]>=1 and [#"Month #"]<=9)
then [#"Month #"]+3

else "Not Possible"),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"FY", Order.Descending}, {"Fiscal Month", Order.Ascending}}),
    #"Grouped Rows2" = Table.Group(#"Sorted Rows", {"FY"}, {{"Sum", each List.Sum([Inflation Percent]), type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows2",{{"FY", Int64.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([FY] = 2013 or [FY] = 2014 or [FY] = 2015 or [FY] = 2016)),
    MyYearsList = List.Buffer(#"Filtered Rows1"[FY]),
    MyValuesList = List.Buffer(#"Filtered Rows1"[Sum]),
    Custom1 = #"Filtered Rows1",
    #"Added Custom1" = Table.AddColumn(Custom1, "Custom", each List.Sum(List.Select(MyValuesList, [FY]< List.Max(MyYearsList))))
in
    #"Added Custom1"

I'm having trouble creating a parameter in my List.Sum function that would allow me to only sum inflation for years that are greater than or = to the year of the value in the row. 

 The goal here is to apply different inflation % (to date) to 2013 cost data vs 2014 vs 2015 etc...

[MarkDGaal]

Okay so I've accomplished what I wanted here by using List.Accumulate and List.Range in combination with an [Index] column and changeing the List.Accumulate "seed" = to the [Sum] of the first value in the list (2016's inflation thus far).....

= Table.AddColumn(#"Added Index", "Custom", each 
List.Accumulate(
List.Range(#"Added Index"[Sum],0,[Index]),[Sum], 
(state, current) => current + state))

BUT I still think this should be possbile without having to do an index and a cumulative total using the List.Select function. @ImkeF this seems right up your alley?

Full code:

let
    Source = Web.Page(Web.Contents("https://www.statbureau.org/en/united-states/inflation-tables", [Timeout=#duration(0, 0, 1, 0)])),
    Data0 = Source{0}[Data],
    #"Changed Type3" = Table.TransformColumnTypes(Data0,{{"Year", Int64.Type}, {"Jan", type number}, {"Feb", type number}, {"Mar", type number}, {"Apr", type number}, {"May", type number}, {"Jun", type number}, {"Jul", type number}, {"Aug", type number}, {"Sep", type number}, {"Oct", type number}, {"Nov", type number}, {"Dec", type number}, {"Total", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type3", {"Year"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Attribute] <> "Total")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"Attribute", "Month"}, {"Value", "Inflation Percent"}}),
    #"CC: Month #" = Table.AddColumn(#"Renamed Columns", "Month #", each if [Month] = "Jan" then "1" else if [Month] = "Feb" then "2" else if [Month] = "Mar" then "3" else if [Month] = "Apr" then "4" else if [Month] = "May" then "5" else if [Month] = "Jun" then "6" else if [Month] = "Jul" then "7" else if [Month] = "Aug" then "8" else if [Month] = "Sep" then "9" else if [Month] = "Oct" then "10" else if [Month] = "Nov" then "11" else if [Month] = "Dec" then "12" else "Not Possible" ),
    #"Changed Type" = Table.TransformColumnTypes(#"CC: Month #",{{"Inflation Percent", type number}, {"Month #", Int64.Type}, {"Year", Int64.Type}}),
    #"CC: Fiscal Year" = Table.AddColumn(#"Changed Type", "FY", each if([#"Month #"]=10 or [#"Month #"]= 11 or [#"Month #"] = 12)
then [Year]+1

else if([#"Month #"] <= 9)
then [Year]

else "Not Possible"),
    #"Added Custom" = Table.AddColumn(#"CC: Fiscal Year", "Fiscal Month", each if([#"Month #"]=10)
then 1

else if([#"Month #"]=11)
then 2

else if([#"Month #"]=12)
then 3

else if([#"Month #"]>=1 and [#"Month #"]<=9)
then [#"Month #"]+3

else "Not Possible"),
    #"Sorted Rows" = Table.Sort(#"Added Custom",{{"FY", Order.Descending}, {"Fiscal Month", Order.Ascending}}),
    #"Grouped Rows2" = Table.Group(#"Sorted Rows", {"FY"}, {{"Sum", each List.Sum([Inflation Percent]), type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows2",{{"FY", Int64.Type}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type1", each ([FY] = 2013 or [FY] = 2014 or [FY] = 2015 or [FY] = 2016)),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows1", "Index", 0, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each List.Accumulate(List.Range(#"Added Index"[Sum],0,[Index]),[Sum], (state, current) => current + state))
in
    #"Added Custom1"

result:

[ImkeF · Accepted solution]

Yes, there are always multiple ways in M... 🙂

let
    Source = Web.Page(Web.Contents("https://www.statbureau.org/en/united-states/inflation-tables", [Timeout=#duration(0, 0, 1, 0)])),
    Data0 = Source{0}[Data],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Data0, {"Year"}, "Month", "Inflation Percent"),
    #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Year", type number}, {"Inflation Percent", Int64.Type}}),
    OnlyValidDates = Table.AddColumn(#"Changed Type", "Month #", each Date.Month(Date.From("1-"&[Month]&"-"&Text.From([Year])))),
    #"CC: Fiscal Month" = Table.RemoveRowsWithErrors(OnlyValidDates, {"Month #"}),
    #"CC: Fiscal Year" = Table.AddColumn(#"CC: Fiscal Month", "FY", each Number.RoundDown([#"Month #"]/10)+[Year]),
    #"Added Custom" = Table.AddColumn(#"CC: Fiscal Year", "Fiscal Month", each if([#"Month #"]<10) then [#"Month #"]+3 else [#"Month #"]-9),
    #"Grouped Rows2" = Table.Group(#"Added Custom", {"FY"}, {{"Sum", each List.Sum([Inflation Percent]), type number}}),
    #"Filtered Rows1" = Table.SelectRows(#"Grouped Rows2", each ([FY] >= 2013)),
    #"Added Custom2" = Table.AddColumn(#"Filtered Rows1", "Custom", (ThisRecord) => List.Sum(Table.SelectRows(#"Filtered Rows1", each [FY]>=ThisRecord[FY])[Sum]))
in
    #"Added Custom2"

No need to cater for month numbers outside range 1..12 here, because they would have been removed in the new # "CC: Fiscal Month" step.

[MarkDGaal]

Well that's certainaly more attractive code.

*small correction in #"Changed Type"..... I think you meant to make [Year] Int64.Type and [Inflation Percent] type number...(instead the opposite is in your code)*

Also, thanks for the alternative way of calculating an FY, never thought to do it that way.