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 O...
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

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.