@andersenjeff5 ,
OK, got it. Super fun problem to work through. I worked it through in PQ/M, but now that I’ve got the algorithm, should be straightforward to replicate in DAX if you’d like. Here’s what I did:
Here’s the result:
And here’s the full M code:
let
Source = Table.FromRows(
Json.Document(
Binary.Decompress(
Binary.FromText(
"Tc4xDsAgDAPAr1SZGQghkDyiL0Dsndql/1chUiWvJ1v2GHQ+93sdTIlchWb6pSxhcQOSRSYdpEatgujOOEqLTAPpIQXE9lhhB/LYtwzEeRsrfuJ43hbNDw==",
BinaryEncoding.Base64
),
Compression.Deflate
)
),
let
_t = ((type nullable text) meta [Serialized.Text = true])
in
type table [#"Month Num" = _t, Forecast = _t]
),
#"Changed Type" = Table.TransformColumnTypes(Source, {{"Forecast", Int64.Type}}),
#"Add Index1" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
#"Renamed Columns" = Table.RenameColumns(#"Add Index1", {{"Index", "Index0"}}),
#"Add Index0" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
#"Renamed Columns1" = Table.RenameColumns(#"Add Index0", {{"Index", "Index1"}}),
#"Add Cumul Fcast" = Table.AddColumn(
#"Renamed Columns1",
"Cumul Fcast",
each List.Sum(List.Range(#"Renamed Columns1"[Forecast], 0, [Index1]))
),
#"Changed Type1" = Table.TransformColumnTypes(#"Add Cumul Fcast", {{"Cumul Fcast", Int64.Type}}),
#"Add Modulo CF Batchsize" = Table.AddColumn(
#"Changed Type1",
"CF Modulo",
each Number.Mod([Cumul Fcast], #"Batch Size")
),
#"Add IntDiv CF Batchsize" = Table.AddColumn(
#"Add Modulo CF Batchsize",
"CF IntDiv",
each Number.IntegerDivide([Cumul Fcast], #"Batch Size")
),
#"Feldmann Shift" = Table.NestedJoin(
#"Add IntDiv CF Batchsize",
{"Index0"},
#"Add IntDiv CF Batchsize",
{"Index1"},
"Add IntDiv CF Batchsize",
JoinKind.LeftOuter
),
#"Expanded for Prev CF IntDiv" = Table.ExpandTableColumn(
#"Feldmann Shift",
"Add IntDiv CF Batchsize",
{"CF IntDiv"},
{"CF IntDiv.1"}
),
#"Sorted Rows" = Table.Sort(#"Expanded for Prev CF IntDiv", {{"Index1", Order.Ascending}}),
#"Renamed Columns2" = Table.RenameColumns(#"Sorted Rows", {{"CF IntDiv.1", "CF IntDiv Prev"}}),
#"Add 1st Occur Int Div" = Table.AddColumn(
#"Renamed Columns2",
"1st Occur IntDiv",
each if [CF IntDiv Prev] = null then 0 else if [CF IntDiv] <> [CF IntDiv Prev] then 1 else 0
),
#"Add Accumulated Forecast" = Table.AddColumn(
#"Add 1st Occur Int Div",
"Accumulated Forecast",
each [CF Modulo] + (#"Batch Size" * [1st Occur IntDiv])
),
#"Changed Type2" = Table.TransformColumnTypes(
#"Add Accumulated Forecast",
{
{"CF Modulo", Int64.Type},
{"CF IntDiv", Int64.Type},
{"CF IntDiv Prev", Int64.Type},
{"1st Occur IntDiv", Int64.Type},
{"Accumulated Forecast", Int64.Type}
}
)
in
#"Changed Type2"
I hope this is helpful. Full solution attached.
eDNA Forum - Batch Size Forecast Solution.pbix (23.1 KB)
