cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Did I answer your question? If so, please mark my post as a solution!

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors