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
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))
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
109 | |
107 | |
92 | |
67 |
User | Count |
---|---|
161 | |
129 | |
129 | |
92 | |
91 |