Forum Discussion
Sumifs in power query
Hi all!
I'm working in the automation of a report. Everything has gone well, but I haven't found a work around for the next problem.
The dataset I'm working with looks like this (with other columns, these not being used in the calculation). There are multiple ID's and the date range goes to 2 years in the future.
| iD | week star date | Forecast value |
| A | 12/4/23 | 1500 |
| B | 12/4/23 | 1600 |
| C | 12/4/23 | 2000 |
| A | 12/11/23 | 2000 |
I have loaded this table in a sheet and created a new column named "12 Weeks Forecast" which is just the sum of the ID values for the next 12 weeks using the following formula :
=SUMIFS([Total Demand],[ID],[@ID],[Week_Start_Date],">"&[@[Week_Start_Date]],[Week_Start_Date],"<="&[@[Week_Start_Date]]+84)
so the criteria would be:
- ID are the same that the current row
- week start date is greater than the actual row week start date, but it must be equals or less than the actual row week start date plus 84 days (so the next 12 weeks are considered)
the problem is that this causes the report to be very slow, so I'm hoping in finding a way to calculate this column directly in power query, but have not been successful yet.
Is There a way I'm able to perform this in power query?
and if's not, it's possible in power pivot? (Just if's not possible in query)
Thanks for any info
Replaced with slightly faster version.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcyxDcAgDAXRXVwj2XyMUycZw2L/NQiJpQDd6RXnTiclymBllFFVhFpyula24HthSHBMct58XMAwho6q0+XnMl8ePj7WbRJcZZ0E28utAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"week start date" = _t, #"Forecast value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"week start date", type date}, {"Forecast value", Int64.Type}}), sumifs = (tbl as table) => Table.AddColumn( tbl, "12 week forecast", each List.Sum(Table.SelectRows( tbl, (r) => r[week start date] > [week start date] and r[week start date] <= Date.AddDays([week start date],84) )[Forecast value] ) ) , group1 = Table.Group(#"Changed Type", {"ID", "week start date"}, {"Forecast value", each List.Sum([Forecast value])}), // first group by ID and dates then by just id. you could skip this grouping if there you do not have multiple rows with the same ID and date. group2 = let buffer = Table.Buffer(group1) in Table.Group( buffer, "ID", {"sumifs", each sumifs(_)}), #"Removed Other Columns" = Table.SelectColumns(group2,{"sumifs"}), #"Expanded sumifs" = Table.ExpandTableColumn(#"Removed Other Columns", "sumifs", {"ID", "week start date", "Forecast value", "12 week forecast"}, {"ID", "week start date", "Forecast value", "12 week forecast"}) in #"Expanded sumifs"hi, Anonymous a little bit complicated but I tried to speed things up. Replace your_table with your table or last step name.
// this function calculates adds "weeks fc" column as running total for upcoming number of weeks wks_forecast = (tbl, weeks) => [rows = List.Buffer(Table.ToRecords(tbl)), values = List.Buffer(tbl[Forecast value]), gena = List.Generate( () => [i = 0, fc = try List.Sum(List.Range(values, 1, weeks)) otherwise 0, rec = rows{i} & [weeks fc = fc]], (x) => rows{x[i]}? <> null, (x) => [i = x[i] + 1, fc = x[fc] - values{i} + (values{i + weeks}? ?? 0), rec = rows{i} & [weeks fc = fc]], (x) => x[rec] ), t = Table.FromRecords(gena)][t], // here we group by ID ans run our function g = Table.Group(your_table, "ID", {"fcast", each wks_forecast(Table.Sort(_, "week start date"), 12)}), // combine things together z = Table.Combine(g[fcast])
2 Replies
Replaced with slightly faster version.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XcyxDcAgDAXRXVwj2XyMUycZw2L/NQiJpQDd6RXnTiclymBllFFVhFpyula24HthSHBMct58XMAwho6q0+XnMl8ePj7WbRJcZZ0E28utAw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, #"week start date" = _t, #"Forecast value" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"week start date", type date}, {"Forecast value", Int64.Type}}), sumifs = (tbl as table) => Table.AddColumn( tbl, "12 week forecast", each List.Sum(Table.SelectRows( tbl, (r) => r[week start date] > [week start date] and r[week start date] <= Date.AddDays([week start date],84) )[Forecast value] ) ) , group1 = Table.Group(#"Changed Type", {"ID", "week start date"}, {"Forecast value", each List.Sum([Forecast value])}), // first group by ID and dates then by just id. you could skip this grouping if there you do not have multiple rows with the same ID and date. group2 = let buffer = Table.Buffer(group1) in Table.Group( buffer, "ID", {"sumifs", each sumifs(_)}), #"Removed Other Columns" = Table.SelectColumns(group2,{"sumifs"}), #"Expanded sumifs" = Table.ExpandTableColumn(#"Removed Other Columns", "sumifs", {"ID", "week start date", "Forecast value", "12 week forecast"}, {"ID", "week start date", "Forecast value", "12 week forecast"}) in #"Expanded sumifs"hi, Anonymous a little bit complicated but I tried to speed things up. Replace your_table with your table or last step name.
// this function calculates adds "weeks fc" column as running total for upcoming number of weeks wks_forecast = (tbl, weeks) => [rows = List.Buffer(Table.ToRecords(tbl)), values = List.Buffer(tbl[Forecast value]), gena = List.Generate( () => [i = 0, fc = try List.Sum(List.Range(values, 1, weeks)) otherwise 0, rec = rows{i} & [weeks fc = fc]], (x) => rows{x[i]}? <> null, (x) => [i = x[i] + 1, fc = x[fc] - values{i} + (values{i + weeks}? ?? 0), rec = rows{i} & [weeks fc = fc]], (x) => x[rec] ), t = Table.FromRecords(gena)][t], // here we group by ID ans run our function g = Table.Group(your_table, "ID", {"fcast", each wks_forecast(Table.Sort(_, "week start date"), 12)}), // combine things together z = Table.Combine(g[fcast])