Identifying Stock On Hand

[RobRayborn]

I am attempting to put together a data table that will provide me with the Stock On Hand that was on hand at any date I choose.
I have two SQL Data inputs. The first one provides me with the Stock On Hand right now.
The second SQL data is transactional, with columns: Part_ID, Date_Time_Created, and Quantity. 
Using the Stock On Hand right now and the transactions provided by the second table, how can I reduce and add the incoming Programing_ID stock transactions (Positive and negative transacitons) to determine how many of the Part_ID I had on hand?

[Zanqueta]

Hi .To calculate Stock On Hand for any past date, calculate stock backwards from the current Stock On Hand. Use a DAX measure that subtracts all transactions occurring after the selected date. This method requires no pre‑calculated historical tables and works directly with your current stock table and transaction table.

You need:

• A table with the current Stock On Hand per Part_ID
• A transaction table with Part_ID, Date_Time_Created, and Quantity
  You should also include a proper Date table for filtering.

   

  Stock On Hand At Date :=

  VAR SelectedDate =

      MAX ( 'Date'[Date] )

  VAR CurrentStock =

      CALCULATE (

          SUM ( StockNow[Stock_On_Hand] )

      )

  VAR TransactionsAfterDate =

      CALCULATE (

          SUM ( Transactions[Quantity] ),

          Transactions[Date_Time_Created] > SelectedDate

      )

  RETURN

      CurrentStock - TransactionsAfterDate

  This approach performs well and allows you to analyse inventory levels across any point in time using standard Power BI slicers.

@RobRayborn