Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating current inventory for a particular date

I have a warehouse transaction table having columns such as date_of_transaction and quantity bought or sold. The positive value of quantity means the product was bought and negative value means the product was sold.

DateQuantity
24 July 20194000
26 July 20196000
30 July 2019-2000
4 August 2019-1000
6 August 201910000

I want to create another table from this table that will give me the current stock in the warehouse(all products combined) on a certain date. The output table should have all the dates in a serially order. The table should look like this:

DateStock Available(Current Inventory)
24 July 20194000
25 July 20194000
26 July 201910000
27 July 201910000
28 July 201910000
29 July 201910000
30 July 20198000
31 July 20198000
1 August 20198000
2 August 20198000
3 August 20198000
4 August 20197000
5 August 20197000
6 August 201917000

The dates on which the transaction didnt occur should also be there in the output table. How should I do it?

  • dedelman_clng's avatar
    dedelman_clng
    6 years ago

    Hi Anonymous -

     

    If you want it as a table in your model, do the following

     

    1) Create a date table (CALENDARAUTO() usually works well). Mark this new table as a Date table and give it a relationship to your data table (it will probably be 1-to-1, but that's fine for what we're doing).

     

    2) Create a new calculated table

    InvHistory =
ADDCOLUMNS (
    DateTab,
    "Stock Available", TOTALYTD ( SUM ( Inventory[Quantity] ), DateTab[Date] )
)

     

    Hope this helps

    David

  • dedelman_clng's avatar
    dedelman_clng
    6 years ago

    Hi Anonymous  - 

     

    The general pattern for a cumulative total that does not reset each year is

     

    Stock Available =
CALCULATE (
    SUM ( Inventory[Quantity] ),
    FILTER ( ALL ( DateTab ), DateTab[Date] <= MAX ( DateTab[Date] ) )
)

     

    Hope this helps

    David

5 Replies

  • dedelman_clng's avatar
    dedelman_clng
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous -

     

    If you want it as a table in your model, do the following

     

    1) Create a date table (CALENDARAUTO() usually works well). Mark this new table as a Date table and give it a relationship to your data table (it will probably be 1-to-1, but that's fine for what we're doing).

     

    2) Create a new calculated table

    InvHistory =
ADDCOLUMNS (
    DateTab,
    "Stock Available", TOTALYTD ( SUM ( Inventory[Quantity] ), DateTab[Date] )
)

     

    Hope this helps

    David

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi. Your solution definitely did the trick for me thanks.

      I just wanted to know what changes would I have to make in order to use this formula for multi-year scenario. I have data ranging right from 2017 to 2020.

      Because it is only calculating for a single year.

      Thanks

       

      • dedelman_clng's avatar
        dedelman_clng
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi Anonymous  - 

         

        The general pattern for a cumulative total that does not reset each year is

         

        Stock Available =
CALCULATE (
    SUM ( Inventory[Quantity] ),
    FILTER ( ALL ( DateTab ), DateTab[Date] <= MAX ( DateTab[Date] ) )
)

         

        Hope this helps

        David

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Join with date table and try this measure

    Stock Available(Current Inventory = CALCULATE(lastnonblankvalue(Table[Date], MAX(Table[Quantity]),filter(date,date[date] <=maxx(date,date[date])))