Forum Discussion

Petanek333's avatar
Petanek333
Helper III
4 years ago
Solved

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 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. 

Need Help
Show and Tell
  • PaulDBrown's avatar
    PaulDBrown
    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 - _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

     

6 Replies

  • Petanek333's avatar
    Petanek333
    Helper III
    4 years ago

    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?

  • Petanek333's avatar
    Petanek333
    Helper III
    4 years ago

    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's avatar
      PaulDBrown
      Community Champion
      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 - _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's avatar
        Petanek333
        Helper III
        4 years ago

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