Forum Discussion

DineshJadhav's avatar
DineshJadhav
Frequent Visitor
8 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
    8 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
    )
     
     
techies's avatar
techies
Icon for Super User rankSuper User
8 months ago

Yes, i can access now, thank you.

 

Will review and get back shortly.

 

 

techies's avatar
techies
Icon for Super User rankSuper User
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
)
 
 
  • DineshJadhav's avatar
    DineshJadhav
    Frequent Visitor
    7 months ago

    Hi techies 
    Thank you for you response. 
    I am still facing the same issue Stock balance is still not carrying forward to next month as opening inventory.
    Could you please share the file so that I can check how you applied this logic?

      • DineshJadhav's avatar
        DineshJadhav
        Frequent Visitor
        7 months ago

        Hi techies ,
        Thank you for the file
        Currently DAX is calculating correct stock balance only for first month. but for Mar 2026 Stock balance should be 
        Stock balance from Feb 2026 + Supply Mar 2026 - Demand Mar 2026
        4,925,741+598,916-527,775 = 4,996,882 currently it is just showing Net movement as stock balance for Mar 2026. It is not considering as last months stock balance and carry it forward to next month