Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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)
Sales orders must consume a forecast in the same week and the previous weeks. (Case 4)
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)
Solved! Go to 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}})
(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}})
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |