cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Djpejsen
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)

 

2021-02-10_22-48-11.png

 

 

 

1 ACCEPTED SOLUTION
Djpejsen
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}})

View solution in original post

2 REPLIES 2
Djpejsen
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}})

View solution in original post

v-deddai1-msft
Community Support
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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.