Forum Discussion

DineshJadhav's avatar
DineshJadhav
Frequent Visitor
7 months ago
Solved

Inventory and Stock balance calculation

I have three tables Invenotry,Supply and Demand by SKUs https://docs.google.com/spreadsheets/d/1a4tus4q_D2BjoVrkTvgMSytNinJUG0CB/edit?usp=drive_link&ouid=100940992842064054172&rtpof=true&sd=true  1...
  • Anonymous's avatar
    Anonymous
    7 months ago

    Hi DineshJadhav ,

    Thank you for following up.
    I’ve attached the PBIX file, which contains the full working setup, including the sample data model, relationships, base measures, and the final running stock balance calculation.

    This approach anchors the calculation to the opening inventory of the first visible month and then accumulates the net movement (Supply minus Demand) up to the current month. By adding this cumulative movement to the opening inventory, the balance rolls forward correctly each month without referencing the previous month’s result directly.

    Please find the attached .pbix file below.


  • techies's avatar
    techies
    7 months ago

    Hi DineshJadhav please try this

     

    Stock Balance by SKU =
    VAR CurrentMonth =
        MAX ( 'Date Table'[Month Index] )

    VAR FirstMonth =
        CALCULATE (
            MIN ( 'Date Table'[Month Index] ),
            ALLSELECTED ( 'Date Table'[Month Index] )
        )

    RETURN
    SUMX (
        VALUES ( SKU[SKU Code] ),

        VAR OpeningInventory =
            CALCULATE (
                SUM ( Inv[Inventory On Hand] ),
                'Date Table'[Month Index] = FirstMonth
            )

       VAR MovementToDate =
        CALCULATE (
            [Net Movement],
            FILTER (
                ALL ( 'Date Table'[Month Index] ),
                'Date Table'[Month Index] >= FirstMonth
                    && 'Date Table'[Month Index] <= CurrentMonth
            )
        )

        RETURN
            OpeningInventory + MovementToDate
    )
     
     
Anonymous's avatar
Anonymous
Not applicable
7 months ago

Hi DineshJadhav ,

Thank you for following up.
I’ve attached the PBIX file, which contains the full working setup, including the sample data model, relationships, base measures, and the final running stock balance calculation.

This approach anchors the calculation to the opening inventory of the first visible month and then accumulates the net movement (Supply minus Demand) up to the current month. By adding this cumulative movement to the opening inventory, the balance rolls forward correctly each month without referencing the previous month’s result directly.

Please find the attached .pbix file below.


Anonymous's avatar
Anonymous
Not applicable
7 months ago

HI DineshJadhav ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 months ago

    Hi DineshJadhav ,

    I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you

     

    • DineshJadhav's avatar
      DineshJadhav
      Frequent Visitor
      6 months ago

      Hi Anonymous,
      This is working for Me. Sorry I forget to reply on this. 

      Thank you. Really Appreciate your help.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 months ago

        Hi DineshJadhav ,

        Glad to hear it’s working for you. Thanks for the update.
        Feel free to reach out if you need any further help.

        Best regards,
        v-sshirivolu