cancel
Showing results for
Search instead for
Did you mean: Frequent Visitor

## PQ - iterate over transactions and use aggregate sum as target

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) 1 ACCEPTED SOLUTION Frequent Visitor

(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}})

2 REPLIES 2 Frequent Visitor

(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}})  Community Support

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  ## Helpful resources

Announcements #### Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group #### The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings. #### Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event. Top Solution Authors
Top Kudoed Authors
Users online (14,643)