Calculating earlier values

[sjehanzeb]

my data is obtained from investment in stock exchange. i need to calculate profit on each selling/settlement.


Sample Data

Date	Scrip	Activity Type	Nos	Amount	Expected Output PROFIT / LOSS (Avg Rate)	Calculated Column SHARE ON HAND
1-Jan-20	PPP	BUY	1000	12000
2-Jan-20	PPP	BUY	2000	23000
3-Jan-20	DDD	BUY	1000	600
3-Jan-20	PPP	SELL	2500	30000	833.33 *1
4-Jan-20	PPP	BUY	500	6000
5-Jan-20	DDD	SELL	1000	400	-200 *2
5-Jan-20	PPP	SELL	1000	12000	250*3

Formula = Sales Amount - (Average Rate of Purchase of stock on hand x No. of Share sold)

*1    33000 -  ((12000+23000)/3000 * 2500) = 833.33

*2     400 - (600/1000 * 1000)  =  -200

*3     12000 -      ((23000/2000 * 500) + (6000/500 *500))/1000) * 1000

I need to calculate profit/loss on every sell. either through average or fifo method.  

Probable solution is to have a caculated column to calculate no. of shares on hand on each date, from there we can calculate cost of purchase of each instance. 

[amitchandak]

@sjehanzeb , Try as a new column if this can help

column =
var _amt = sumx(filter(Table,[Script] =earlier([Script]) , [Date] <earlier([Date])),if([Activity] = "Buy",[Amount] ,=-1*[Amount]))
var _Nos = sumx(filter(Table,[Script] =earlier([Script]) , [Date] <earlier([Date])),if([Activity] = "Buy",[Nos] ,=-1*[Nos]))
return
[Amount] - (Divide(_amt,_Nos)*[Nos])

[Greg_Deckler]

@sjehanzeb What is your expected output?