Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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?
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!
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))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
211 | |
86 | |
64 | |
59 | |
56 |