Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
KennethMau
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:

 

 

IDWeek_Start_DateDemandInitial StockReceipts
CL23-6000000001412612/11/2023000
CL23-6000000001412712/11/2023000
CL23-6000000002241912/11/2023000
CL23-6000000002615112/11/2023000
CL23-6000000010452712/11/2023000
CL23-6000000010827912/11/2023000
CL23-6000000010848512/11/2023000
CL23-6000000011036612/11/2023000
CL23-6000000011934612/11/2023000
CL23-6000000012288212/11/2023000
CL23-6000000012397412/11/2023000
CL23-6000000012420512/11/2023000
CL23-6000000012420712/11/2023000
CL23-6000000012431212/11/2023000

 

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
mh2587
Super User
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!




LinkedIn Icon
Muhammad Hasnain



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

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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