Calculate the closing stock for different products

[KennethMau]

Hello all!

Im having a hard time trying to calculate the closing stock of diferent products for each week, my dataset looks like this:

ID	Week_Start_Date	Demand	Initial Stock	Receipts
CL23-60000000014126	12/11/2023	0	0	0
CL23-60000000014127	12/11/2023	0	0	0
CL23-60000000022419	12/11/2023	0	0	0
CL23-60000000026151	12/11/2023	0	0	0
CL23-60000000104527	12/11/2023	0	0	0
CL23-60000000108279	12/11/2023	0	0	0
CL23-60000000108485	12/11/2023	0	0	0
CL23-60000000110366	12/11/2023	0	0	0
CL23-60000000119346	12/11/2023	0	0	0
CL23-60000000122882	12/11/2023	0	0	0
CL23-60000000123974	12/11/2023	0	0	0
CL23-60000000124205	12/11/2023	0	0	0
CL23-60000000124207	12/11/2023	0	0	0
CL23-60000000124312	12/11/2023	0	0	0

I want to create a new calculated column named closign stock. This column can never be negative (in this case i want a 0).

for the first week of each ID, i want to use the value in initial stock, because that the real closing stock for week 1. for the next week, based on the closing stock for week 1, [closing stock of previous week ] -[Total demand] + [Total receipts]. is there a way to perform this?

[mh2587]

Closing Stock = //Try this one
VAR CurrentID = YourTable[ID]
VAR CurrentDate = YourTable[Week_Start_Date]
VAR CurrentInitialStock = CALCULATE(SUM(YourTable[Initial Stock]), FILTER(YourTable, YourTable[ID] = CurrentID && YourTable[Week_Start_Date] = CurrentDate))
VAR PreviousClosingStock = CALCULATE(SUM(YourTable[Closing Stock]), FILTER(YourTable, YourTable[ID] = CurrentID && YourTable[Week_Start_Date] < CurrentDate))
VAR TotalDemand = SUM(YourTable[Demand])
VAR TotalReceipts = SUM(YourTable[Receipts])
VAR CalculatedClosingStock = PreviousClosingStock - TotalDemand + TotalReceipts
RETURN
    IF(CalculatedClosingStock < 0, 0, IF(ISBLANK(CurrentInitialStock), CalculatedClosingStock, CurrentInitialStock))

[KennethMau]

Hi  Muhammad!

I dont have a column named "Closing Stock". That the actual column i want to get

VAR PreviousClosingStock = CALCULATE(SUM(YourTable[Closing Stock]), FILTER(YourTable, YourTable[ID] = CurrentID && YourTable[Week_Start_Date] < CurrentDate))