Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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