PQ - iterate over transactons and use aggregrate sum as target

Question

Hi guys

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

There is general 4 conditions for the iteration

Condition 1

Sales order in WK02 of 10 pcs

Forecast before adjustment
W01 : 50
W02 : 50
W03 : 50
W04 : 50
Aggregate sum 200

Forecast after adjustment
W01 : 50
W02 : 40 (Forecast)
W02 : 10 (Sales order)
W03 : 50
W04 : 50
Aggregate sum 200
——————————————
Condition 2

Sales order in WK02 of 60 pcs

Forecast before adjustment
W01 : 50
W02 : 50
W03 : 50
W04 : 50
Aggregate sum 200

Forecast after adjustment
W01 : 50
W02 : 0 (Forecast)
W02 : 60 (Sales order)
W03 : 40 (Forecast)
W04 : 50
Aggregate sum 200

——————————————
Condition 3

Sales order in WK02 of 110 pcs

Forecast before adjustment
W01 : 50
W02 : 50
W03 : 50
W04 : 50
Aggregate sum 200

Forecast after adjustment
W01 : 50
W02 : 0 (Forecast)
W02 : 110 (Sales order)
W03 : 0 (Forecast)
W04 : 40
Aggregate sum 200

——————————————
Condition 4

Sales order in WK02 of 50 pcs

Forecast before adjustment
W01 : 50
W02 : 50
W03 : 50
W04 : 50
Aggregate sum 200

Forecast after adjustment
W01 : 50
W02 : 0 (Forecast)
W02 : 50 (Sales order)
W03 : 50
W04 : 50
Aggregate sum 200

Answer 1

Hi @Djpejsen
Could you play with this workbook and let me know? With the hope this is good this time as I run out of aspirin :-)

Answer 2

Hi @Djpejsen

Is it possible to have both [Correction] and [Rest] in the output? ToColumns = Table.ToColumns(Source) & {Correction} & {Rest}

(a never ending story :) I don't know where you are (cf. The if statements have been adjusted + our previous exchanges) so I kept the same example you Accepted as answer and revised the corresponding BuildCorrection function - Workbook avail. here

(SourceTable as table) as table =>  
let  
    Source = SourceTable,  
    Forecast = Table.Column(Source, "Current FC"),  
    CorrectionRest = List.Generate(  
    ()=>  
        [  
            i = 0,  
            forecast = Forecast{i},  
            sales = Table.First(Source)[Sales Order],  
            Correction = if sales > 0 then -forecast else 0,  
            Rest = if sales = 0 then 0  
            else 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 [Rest]+forecast  
                    else List.Max({[Rest]-forecast, 0})  
            ],  
        each [ [Correction], [Rest] ]  
    ),  
    ToTable = Table.FromColumns(  
        Table.ToColumns(SourceTable) &  
        Table.ToColumns(Table.FromRecords(CorrectionRest, type table [Correction=Int64.Type, Rest=Int64.Type])),  
        Table.ColumnNames(SourceTable) & {"Correction", "Rest"}  
    )  
in  
    ToTable