Forum Discussion

DineshJadhav's avatar
DineshJadhav
Frequent Visitor
6 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
    6 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
    6 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
6 months ago

Hi Anonymous,
Thank you for the input.It will be really helpfull if can suggest correct DAX

FBergamaschi's avatar
FBergamaschi
Icon for Super User rankSuper User
6 months ago

Hi DineshJadhav,

to provide the correct DAX we need more information.

 

DAX has different results based on the context, so we need to know the context in which your calculation is performed. Please provide a clear set of images showing which columns are grouped in the visual where you are using the measure, and any slicer interacting, a picture fo the data model and a short description on each image, so we can get the necessary insights.

 

Thank you.

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI