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?

[v-priyankata]

Hi @RobRayborn 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@Zanqueta Thanks for the inouts.

I hope the information provided by user was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @RobRayborn 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[Zanqueta · Accepted solution]

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