PQ - iterate over transactions and use aggregate sum as target

[Djpejsen]

Hi guys

I have some forecast transactions that I need to adjust if a Sales Order is in the same week as forecast, based on the Sales order qty. and the aggregate sum

below you will find the logic.

1. The accumulated sum of current forecast qty. is the target, EXCEPT if the accumulated sum of Sales orders is greater than the accumulated sum of the current forecast. (See case 3)

2. Sales orders must consume a forecast in the same week and the previous weeks. (Case 4)

3. If Sales order qty. is less than Forecast qty. then move the remaining forecast to the next week or to the following weeks. (case 2)

[Djpejsen · Accepted solution]

(SourceTable as table) as number =>
let
Source = Table.Buffer(SourceTable),
Forecast = Table.Column(Source, "FC"),
Sales = Table.Buffer(Table.First(Source)[SO]),
Correction = List.Generate(
()=>
[
i = 0,
forecast = Forecast{i},
correction = if Sales = null or Sales = 0 then 0
else -forecast ,
rest = if Sales = null or Sales = 0 then 0
else List.Sum({forecast,-Sales})
],
each [i] < List.Count(Forecast),
each
[
i = [i] + 1,
forecast = Forecast{i},

correction = if [rest] = 0 then 0
else if [rest] > 0 then List.Min({forecast, [rest]})
else List.Max({-forecast, [rest]}),

rest =
if [rest] < 0 then if [rest] > -forecast then 0
else List.Sum({[rest],forecast})
else List.Max( {[rest],-forecast}, 0)
],
each [correction]
),
ToColumns = Table.ToColumns(Source) & {Correction},
ToTable = Table.FromColumns(ToColumns, Table.ColumnNames(Source) & {"Correction"})

in
Table.TransformColumnTypes(ToTable, {{"Correction", Int64.Type}})

[v-deddai1-msft]

Hi @Djpejsen ,

I can't get your requirements well. Would you please show us sample data (pbix file)by onedrive for business? And also please show us accurate expected output based on your sample data.

I can't understand your first two logic .

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai