Weighted average price over the time | Recursive Measure

Question

Hi everyone,&nbsp;I have a table that summarizes my data per product type, date, quantity purchased, average price of purchasing, quantity sold, average price of sales and quantity balance. I would like create a measure that in anyway self-reference itself to calculate the average price of quantity balance over the time.&nbsp;I have the following sample data example in Excel:G12=&nbsp;((F12-B12)*G11+(B12*C12))/F12Measure:= [(200-100)*25,27 + 100*25,07] / 200&nbsp;I'm strugling to performa a measure like that so I would really appreciate your help 🙂&nbsp;Row/ColABCDEFG1&nbsp;QTY PurAvg PurQTY SalAvg SalBalanceMeasure2A30010,2130010,3200,00301/01/202010010,2500,0010010,25416/01/202020010,1900,0030010,21528/01/2020010,15010,2530010,21604/02/2020010,3510010,3020010,21721/02/20200020010,4000,008B50025,2120025,2730025,16901/01/202020025,3100,0020025,311016/01/202010025,200,0030025,271128/01/20200020025,2710025,271204/02/202010025,0700,0020025,171321/02/202010025,1500,0030025,1614C60017,9520018,0340017,951501/01/202030017,9300,0030017,931616/01/20200010018,0120017,931728/01/20200010018,0510017,931821/02/202030017,9600,0040017,9519D5005,9406,105005,932001/01/20201005,9800,001005,982116/01/20201005,900,002005,942228/01/20201005,8600,003005,912304/02/20201005,8900,004005,912421/02/20201006,0100,005005,9325Total190014,3870014,051200&nbsp;

lbendlin · Answer

What is the formula for G9?&nbsp; Fx -Bx is the previous balance, so F12-B12 is the same as F11 ?

heitorodriguess · Answer

Hello&nbsp;lbendlin&nbsp;The general formula would be the following:&nbsp;[(Balance - QTY Pur) * Previous Measure Value for that Item + (QTY Pur * AvgPur)] / Balance&nbsp;For G9 as there is no previous measure value for Item B, would be only&nbsp;(QTY Pur * AvgPur)] / Balance:(200*25,31)/200=25,31&nbsp;Fx - Bx is equal the previous balance only in case there is no QTY Sal&nbsp;I hope now it´s more clear.Thanks in advance!

Forum Discussion

Weighted average price over the time | Recursive Measure

2 Replies

Related content

Calculating Weighted Average

Weighted Average DAX

Weighted Average Measure

Recursion Strategy

Weighted average by month

Row/Col	A	B	C	D	E	F	G
1		QTY Pur	Avg Pur	QTY Sal	Avg Sal	Balance	Measure
2	A	300	10,21	300	10,32	0	0,00
3	01/01/2020	100	10,25	0	0,00	100	10,25
4	16/01/2020	200	10,19	0	0,00	300	10,21
5	28/01/2020	0	10,15	0	10,25	300	10,21
6	04/02/2020	0	10,35	100	10,30	200	10,21
7	21/02/2020	0	0	200	10,40	0	0,00
8	B	500	25,21	200	25,27	300	25,16
9	01/01/2020	200	25,31	0	0,00	200	25,31
10	16/01/2020	100	25,2	0	0,00	300	25,27
11	28/01/2020	0	0	200	25,27	100	25,27
12	04/02/2020	100	25,07	0	0,00	200	25,17
13	21/02/2020	100	25,15	0	0,00	300	25,16
14	C	600	17,95	200	18,03	400	17,95
15	01/01/2020	300	17,93	0	0,00	300	17,93
16	16/01/2020	0	0	100	18,01	200	17,93
17	28/01/2020	0	0	100	18,05	100	17,93
18	21/02/2020	300	17,96	0	0,00	400	17,95
19	D	500	5,94	0	6,10	500	5,93
20	01/01/2020	100	5,98	0	0,00	100	5,98
21	16/01/2020	100	5,9	0	0,00	200	5,94
22	28/01/2020	100	5,86	0	0,00	300	5,91
23	04/02/2020	100	5,89	0	0,00	400	5,91
24	21/02/2020	100	6,01	0	0,00	500	5,93
25	Total	1900	14,38	700	14,05	1200