Summing values in a column between two dates in Power Query

[mafaber]

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!

[mafaber · Accepted solution]

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 totals

  RunFunction = (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 function

  RunTotals = 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)