Hello.

Using a snapshot of inventory + PO's - orders received, I would like to be able to forecast inventory in a pivot table, so that I can project future stock levels at any given time.

E.g

if I have 10kgs on hand in January (as of today), and I have 15kgs expected to be be received in February, and then 25kgs to ship in March, I would want a table showing that in January I have a closing balance of 10, in February I have a closing balance of 25, and in March I have a closing balance of 0.

I have created a movements table in power query, with transaction dates and transaction types, but I can't work out how to display a "running total" in power pivot as described above.

Movements Table would look like:

Code - Transaction Type - Transaction Date - Qty (+/-)  

A         On Hand                  01/01              10

A         PO                         01/02                15

A         Sale                       01/03                -25

Ideal output

Code            Month

                       1      2      3

A                   10     25     0

Then I would like to be able to slice the data, based on status of the stock, i.e expired etc, so would need to be considered when building the DAX formula.

Any assistance appreciated.