Forum Discussion
Calculating stock based on weekly changes
- 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
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 - _RunningStock
Or...
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 - _PW
Or 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
Paul, I really appreciate your help. Thank you very much!