Forum Discussion
Summing values in a column between two dates in Power Query
Hi,
I have a table with a demand plan per product per week and with a certain range in days per product telling how long into the future I need to consider the demand. What I would like to do is to calculate in a column a kind of rolling demand per product per week:
Table:
| Product | Range of Coverage in days | Week | Week start date | Demand end date (start date+RoC) | Weekly Demand | Rolling Demand |
| aaa | 14 | 01 | 2021.10.04. | 2021.10.18. | 10 | (w01+w02) = 25 |
| aaa | 14 | 02 | 2021.10.11. | 2021.10.25. | 15 | 40 |
| aaa | 14 | 03 | 2021.10.18. | 2021.11.01. | 25 | 30 |
| aaa | 14 | 04 | 2021.10.25. | 2021.11.08. | 5 | 5 + w05 |
| bbb | 21 | 01 | 2021.10.04. | 2021.10.18. | 100 | (w01+w02+w03) = 550 |
| bbb | 21 | 02 | 2021.10.11. | 2021.10.25. | 200 | 600 |
| bbb | 21 | 03 | 2021.10.18. | 2021.11.01. | 250 | 400 + w05 |
| bbb | 21 | 04 | 2021.10.25. | 2021.11.08. | 150 | 150 + w05 + w06 |
Demand end date is calculated based on range of coverage of the specific product and the start date of the week, the calculation what I am looking for is the Rolling Demand. (the numbers only of course, text is just explanation)
So basically I need the sum of a value column where the week start date is between week start date and demand end date, and I have to calculate it in Power Query (Power BI Dataflow), not in DAX.
Thank you for any help!
I solved it myself using a custom function.
#"Grouped rows 1" = Table.Group(#"Replaced value", {"material_no"}, {{"data", each _, type nullable table}}),// Function to calculate running totalsRunFunction = (RunTable as table) as table =>let#"Added index" = Table.AddIndexColumn(RunTable, "index", 0, 1, type number),#"Added custom" = Table.AddColumn(#"Added index", "demand_running_sum", each List.Sum(List.Range(#"Added index"[value_of_week], [index], [range_of_coverage_weeks])))in#"Added custom",// Call the functionRunTotals = Table.TransformColumns(#"Grouped rows 1", {"data", each RunFunction(_)})where the custom column calculates the demand starting from that row (index starting from 0) and as long as needed per material (range of coverage in weeks)
1 Reply
I solved it myself using a custom function.
#"Grouped rows 1" = Table.Group(#"Replaced value", {"material_no"}, {{"data", each _, type nullable table}}),// Function to calculate running totalsRunFunction = (RunTable as table) as table =>let#"Added index" = Table.AddIndexColumn(RunTable, "index", 0, 1, type number),#"Added custom" = Table.AddColumn(#"Added index", "demand_running_sum", each List.Sum(List.Range(#"Added index"[value_of_week], [index], [range_of_coverage_weeks])))in#"Added custom",// Call the functionRunTotals = Table.TransformColumns(#"Grouped rows 1", {"data", each RunFunction(_)})where the custom column calculates the demand starting from that row (index starting from 0) and as long as needed per material (range of coverage in weeks)