Skip to main content
cancel
Showing results for 
Search instead 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

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors