@jmcauley88
This code I give you is ugly and terrible from the maintenance point of view because this should really be carried out in Power Query. On top of that, you have not provided a representative sample of your data since you only have one SKU, one Market, one Customer and one Forecast Name in the table.
Closing Stock =
var vShipmentEndDate = MAX( T[Forecast Shipment Date] )
var vSKUsWithFirstStock =
FILTER(
ADDCOLUMNS(
SUMMARIZE(
T,
T[SKU],
T[Customer],
T[Forecast Name],
T[Market]
),
"@FirstStockDate",
CALCULATE(
MIN( T[Forecast Shipment Date] ),
T[Forecast Type] = "Stock",
T[Forecast Shipment Date] <= vShipmentEndDate,
ALLEXCEPT(
T,
T[SKU],
T[Customer],
T[Forecast Name],
T[Market]
)
)
),
NOT ISBLANK( [@FirstStockDate] )
)
var vResult =
SUMX(
vSKUsWithFirstStock,
var vStartDate = [@FirstStockDate]
return
CALCULATE(
SUMX(
T,
T[9Lc] * (
( T[Forecast Type] = "Stock" ) +
( T[Forecast Type] = "Shipment" ) -
( T[Forecast Type] = "Depletion" )
)
),
T[Forecast Shipment Date] >= vStartDate,
T[Forecast Shipment Date] <= vShipmentEndDate,
ALLEXCEPT(
T,
T[SKU],
T[Customer],
T[Forecast Name],
T[Market]
)
)
)
return
vResult
