Forum Discussion

RobRayborn's avatar
RobRayborn
Helper IV
4 months ago
Solved

Identifying Stock On Hand

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?

Need Help
Tutorial Requests
  • Zanqueta's avatar
    Zanqueta
    4 months ago

    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

3 Replies

  • Zanqueta's avatar
    Zanqueta
    Super User
    4 months ago

    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

  • v-priyankata's avatar
    v-priyankata
    Community Support
    4 months ago

    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's avatar
      v-priyankata
      Community Support
      4 months ago

      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.