Forum Discussion

rajasekar_o's avatar
rajasekar_o
Icon for Helper V rankHelper V
2 years ago

opening quantity closing quantity calculation

hi team, need help for  calculating inventory opening quantity and closing quantity   with out date beet ween date selection opening quantity and closing quantity correctly showing if i select an...
Need Help
Tips and Tricks
Tutorial Requests
Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

 

 

 

In quantity: = 
SUM( Data[In qty] )

 

Out quantity: = 
SUM( Data[Out qty] )

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

Opening quantity: = 
CALCULATE (
    [In quantity:] - [Out quantity:],
    WINDOW (
        1,
        ABS,
        -1,
        REL,
        ALL ( 'Calendar'[Date] ),
        ORDERBY ( 'Calendar'[Date], ASC )
    )
)

 

Closing quantity: = 
[Opening quantity:] + [In quantity:] - [Out quantity:]

 

  • rajasekar_o's avatar
    rajasekar_o
    Icon for Helper V rankHelper V
    2 years ago

    its not showing correct quantity

     

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      2 years ago

      please share your sample pbix file, and then I can try to look into it.

      thanks.