Forum Discussion

rajasekar_o's avatar
rajasekar_o
Helper V
2 years ago

Inventory closing

hi team ,

i need help for calculate inventory opening and closing Quntity .

DateITEMIDPLUSORMINUSQTY
31-03-2017RM ITEM Ap125000
01-04-2022RM ITEM Ap1000
02-04-2022RM ITEM Ap333
02-08-2022RM ITEM Ap200
02-02-2023RM ITEM Am200
11-03-2023RM ITEM Ap100
31-03-2023RM ITEM Ap1100
04-04-2023RM ITEM Ap167
10-04-2023RM ITEM Am81
13-04-2023RM ITEM Am40
04-05-2023RM ITEM Ap50
05-05-2023RM ITEM Am1
20-05-2023RM ITEM Ap55
30-05-2023RM ITEM Ap10
04-07-2023RM ITEM Am10
05-09-2023RM ITEM Ap210
06-09-2023RM ITEM Am515
09-09-2023RM ITEM Am20
15-09-2023RM ITEM Ap160
16-09-2023RM ITEM Ap135
19-09-2023RM ITEM Ap28
20-09-2023RM ITEM Ap10
02-10-2023RM ITEM Ap33


I try following measure 

Opening = CALCULATE(CALCULATE(SUM(STOCKVALUE[Qty]),FILTER(STOCKVALUE,STOCKVALUE[PLUSORMINUS]= "P")) ,WINDOW(0,ABS,-1,REL, ALL(STOCKVALUE[ITEMID], STOCKVALUE[DOCDATE]), ORDERBY([Date]))) - CALCULATE(SUM(STOCKVALUE[Qty]), WINDOW(0,ABS,-1,REL, ALL(STOCKVALUE[ITEMID], STOCKVALUE[DOCDATE]), ORDERBY([Date])), FILTER(STOCKVALUE,STOCKVALUE[PLUSORMINUS]= "M"))
CLosing = CALCULATE(CALCULATE(SUM(STOCKVALUE[Qty]),FILTER(STOCKVALUE,STOCKVALUE[PLUSORMINUS]= "P")) ,WINDOW(0,ABS,0,REL, ALL(STOCKVALUE[ITEMID], STOCKVALUE[DOCDATE]), ORDERBY([Date]))) - CALCULATE(SUM(STOCKVALUE[Qty]), WINDOW(0,ABS,0,REL, ALL(STOCKVALUE[ITEMID], STOCKVALUE[DOCDATE]), ORDERBY([Date])), FILTER(STOCKVALUE,STOCKVALUE[PLUSORMINUS]= "M"))

Opening and closing are not coming correctly.how to solve this

 

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies