Forum Discussion
Petanek333
4 years agoHelper III
Calculating stock based on weekly changes
Hi, I have two tables. One table of current stock to which I created an actual Year-Week column as it is showing the stock right at the moment of data import. and a table of stock changes in ...
- 4 years ago
See if this works for you. (BTW, there was an inactive relationhip in the model you sent, which needs to made active)
Since it's not clear whether you wish to see the value as the initial or the final stock of the week...
Initial weekly Stock = VAR _Current = CALCULATE ( LASTNONBLANKVALUE ( 'WeekYear Start'[Year-Week], [Sum Stock] ), ALL ( 'WeekYear Start' ) ) VAR _RunningStock = CALCULATE ( [Sum Weekly Changes], FILTER ( ALL ( 'WeekYear Start' ), 'WeekYear Start'[Index] >= MAX ( 'WeekYear Start'[Index] ) ) ) RETURN _Current - _RunningStockOr...
Final weekly Stock = VAR _Current = CALCULATE(LASTNONBLANKVALUE('WeekYear Start'[Year-Week], [Sum Stock]), ALL('WeekYear Start')) VAR _RunningStock = CALCULATE([Sum Weekly Changes], FILTER(ALL('WeekYear Start'), 'WeekYear Start'[Index] >= MAX('WeekYear Start'[Index]))) VAR _PW = _RunningStock - [Sum Weekly Changes] RETURN _Current - _PWOr both even
Btw, I the table for the current stock will only ever have one value per product, you can probably use a simple CALCULATE([Sum Stock], ALL(Weekyear Start)) instead of the CALCULATE(LASTNONBLANK... in the _Current variables
I've attached the sample PBIX file
danextian
4 years agoSuper User
- Petanek3334 years agoHelper III
Hi danextian , that is strange, I can still open it, anyway thank you for reporting it.
I have reuploaded the file to my onedrive, hope it helps