Opening Stock as per given input date by user

[vijayit43]

Dear All ,

             We want to create Opening Stock as per given Input date by User. how to achieve it. please suggest. for example.

ITEMNAME	DATEPHYSICAL	QTY
GV500-1502161	30-06-2016	27
GV500-1502161	02-07-2016	-15
GV500-1502161	06-07-2016	1
GV500-1502161	24-07-2016	1
GV500-1502161	25-07-2016	1
GV500-1502161	10-08-2016	1
GV500-1502161	11-08-2016	4
GV500-1502161	17-08-2016	1
GV500-1502161	28-08-2016	-13
GV500-1502161	15-09-2016	-4
GV500-1502161	22-12-2016	-1
GV500-1502161	06-04-2017	-3

if we Select date (07-07-2016) then Opening stock show as 13 how to achieve it please help.

[Vvelarde · Accepted solution]

@vijayit43

Hi, try with this:

Assuming that you have a calendar table to your selection date. (Don't related to your table)

OpeningStock =
CALCULATE (
    SUM ( Table2[QTY] ),
    FILTER ( Table2, Table2[DATEPHYSICAL] < SELECTEDVALUE ( CalendarTable[Date] ) )
)

Regards

Victor

Lima Peru