Calculating stock based on weekly changes

[Petanek333]

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 the past on a weekly basis.

What I need is a table like this (which is not right) ...

... showing week to week stock quantities. On 2022-33 (lets assume it happens at the end of the week) there was 1 unit of product A substracted and 2022-33 is showing 301 units for product A. This is correct. But also weeks 29 to 32 should be showing 301 until week 28, where additional 2 units were substracted and the stock quantity should be 303 before the change. 

I need to create some kind of backward running total that is always gradually subtracted from the original value. I tried, I really tried to come up with something after great advices from @PaulDBrown in another thread. You can see the attached file that I did. But I just still lack the dax skills.

Attached sample report 

Extra question:

Also, I've noticed that if I don't make a relationship active between WeekYear table and CurrentStock table, all the weeks in the table are showing current stock of the original value. That is OK. 

If I make a relationship active between those tables, the result is as following 

and it makes perfect sense since the value of current stock is labeled with that current week.

However, I think it is more professional to keep the relationship active. Is there any way (perhaps a formula to be included in the final DAX expression) to keep it active and let the current stock be shown in all the weeks anyway? 

I hope my question makes sense and thank you very much for any help. 

[Petanek333]

So I thought I solved it, but I realized one mistake that I cannot fix.

First table is current stock, second table are stock movements and the measure on the top of the picture is used in that table and it works fine. 

Thing is I realized that the last week's value in the top table already contains the stock movement. So the running total (running total is a separate measure) should calculate all the selected weeks except for the last week.

I tried modifying the measure like this but it results in the last table 🙂

Can you please help me? @danextian @PaulDBrown  Sample file is attached below.

Sample report 

[PaulDBrown · Accepted solution]

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

[Petanek333]

Paul, I really appreciate your help. Thank you very much!

[Petanek333]

OK, I think I made it, just created a backward Running Total measure like this

Just RT = 
var MinWeek = MIN('WeekYear Start'[Index])
return
    CALCULATE([Sum Weekly Changes],QuantityChangeTable[index to sort]>=MinWeek,ALL('WeekYear Start'))

 and then just summed it up with the sum of current stock, it works 🙂

However, I would like anyone to comment on it and say his or her opinion as I very much appreciate opinions of this community.

And finally, it is all done with the relationship between week table and current stock table. Is it the right to do or is different approach considered best practice?

[danextian]

Hi @Petanek333 ,

Your link returns an error:

[Petanek333]

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

Onedrive file