cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Calculate the closing stock for different products

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?

2 REPLIES 2
Super User
``````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))``````

Proud to be a Super User!

Regular Visitor

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))`

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors