how to calculate daily inventory Opening quantity and closing quantity

[rajasekar_o]

i have the data of inventory 

Date	Item	pluseormines	Qty
31-03-2017	RM ITEM A	p	125000
01-04-2022	RM ITEM A	p	1000
02-04-2022	RM ITEM A	p	333
02-08-2022	RM ITEM A	p	200
02-02-2023	RM ITEM A	m	100
02-02-2023	RM ITEM A	p	100
11-03-2023	RM ITEM A	p	100
31-03-2023	RM ITEM A	p	1100
04-04-2023	RM ITEM A	p	167
10-04-2023	RM ITEM A	m	1
10-04-2023	RM ITEM A	p	80
13-04-2023	RM ITEM A	m	1
13-04-2023	RM ITEM A	p	39
04-05-2023	RM ITEM A	p	50
05-05-2023	RM ITEM A	m	1
20-05-2023	RM ITEM A	p	55
30-05-2023	RM ITEM A	p	10
04-07-2023	RM ITEM A	m	10
05-09-2023	RM ITEM A	p	210
06-09-2023	RM ITEM A	m	160
06-09-2023	RM ITEM A	p	355
09-09-2023	RM ITEM A	m	10
09-09-2023	RM ITEM A	p	10
15-09-2023	RM ITEM A	p	160
16-09-2023	RM ITEM A	p	135
19-09-2023	RM ITEM A	p	28
20-09-2023	RM ITEM A	p	10
02-10-2023	RM ITEM A	p	33

I try but not correct

i try this its not working 

Onhand Opening = CALCULATE(SUM(STOCKVALUE[IN Qty]),filter(all(Calender),Calender[Date] <min(Calender[Date]))) -

CALCULATE(SUM(STOCKVALUE[Out Qty]),filter(all(Calender),Calender[Date] <min(Calender[Date])))

onhand closing = CALCULATE(SUM(STOCKVALUE[QTY]),filter(all(Calender),Calender[Date] <=Max(Calender[Date]))) -

CALCULATE(SUM(STOCKVALUE[Out Qty]),filter(all(Calender),Calender[Date] <= Max(Calender[Date])))

how to calculate inventory opening Qty and Closing Qty

[amitchandak]

@rajasekar_o , With help from a data table

Onhand Opening= CALCULATE(SUM(Table[In]),filter(all(date),date[date] <min(date[date]))) -
CALCULATE(SUM(Table[Out]),filter(all(date),date[date] <min(date[date])))


onhand closing= CALCULATE(SUM(Table[In]),filter(all(date),date[date] <=Max(date[date]))) -
CALCULATE(SUM(Table[Out]),filter(all(date),date[date] <= Max(date[date])))

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

[rajasekar_o]

its not working 

[amitchandak · Accepted solution]

@rajasekar_o , You have to add filter of P and M

Onhand Opening = CALCULATE(SUM(STOCKVALUE[IN Qty]),filter(all(Calender),Calender[Date] <min(Calender[Date]))

,FILTER(STOCKVALUE,STOCKVALUE[pluseormines]= "P")

) -
CALCULATE(SUM(STOCKVALUE[Out Qty]),filter(all(Calender),Calender[Date] <min(Calender[Date]))

,FILTER(STOCKVALUE,STOCKVALUE[pluseormines]= "M")

)

onhand closing = CALCULATE(SUM(STOCKVALUE[QTY]),filter(all(Calender),Calender[Date] <=Max(Calender[Date]))

,FILTER(STOCKVALUE,STOCKVALUE[pluseormines]= "P")

) -
CALCULATE(SUM(STOCKVALUE[Out Qty]),filter(all(Calender),Calender[Date] <= Max(Calender[Date]))

,FILTER(STOCKVALUE,STOCKVALUE[pluseormines]= "P")

)

Using window function without Date

CLosing = CALCULATE(CALCULATE(SUM(STOCKVALUE[Qty]),FILTER(STOCKVALUE,STOCKVALUE[pluseormines]= "P")) ,WINDOW(0,ABS,0,REL, ALL(STOCKVALUE[Item], STOCKVALUE[Date]), ORDERBY([Date]))) - CALCULATE(SUM(STOCKVALUE[Qty]), WINDOW(0,ABS,0,REL, ALL(STOCKVALUE[Item], STOCKVALUE[Date]), ORDERBY([Date])), FILTER(STOCKVALUE,STOCKVALUE[pluseormines]= "M"))

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

The date table is better, as you do not have to worry about other columns like pluseormines in Visual

File attached