Calculating current inventory for a particular date

[Anonymous]

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.

Date	Quantity
24 July 2019	4000
26 July 2019	6000
30 July 2019	-2000
4 August 2019	-1000
6 August 2019	10000

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:

Date	Stock Available(Current Inventory)
24 July 2019	4000
25 July 2019	4000
26 July 2019	10000
27 July 2019	10000
28 July 2019	10000
29 July 2019	10000
30 July 2019	8000
31 July 2019	8000
1 August 2019	8000
2 August 2019	8000
3 August 2019	8000
4 August 2019	7000
5 August 2019	7000
6 August 2019	17000

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

[dedelman_clng · Accepted solution]

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 · Accepted solution]

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]

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 · Accepted solution]

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

[Anonymous]

Thanks. That worked!

[amitchandak]

@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])))