Forum Discussion
Average stock
Hi! I'm trying to calculate the average stock existence of a stock table. I'll filter by period and product the New Measure with the filters in the report panel, but I'm not able to have the weighted average for the number of days.
After this I'll calculate with another measure the stock quantity turnover, having the sum of negative quantities (outgoing items) as numerator of the formula).
Consider that in this database I have several years of data.
Someone can please help me?
6 Replies
There's no measure yet. The calculation from this table is very difficult and must be: (stock quantity*number of days from the date in the line to the next date of product quantity update, in addition to the same calculation done before on the same product)/total number of days selected in the filter.
For example at the 6th line of the table I posted before the result would be: ((120*24)+((120+(-20))*36))/60
60 is the number of days filtered from 01/01 to 01/03
120 is the quantity of the previous step of the product
24 and 36 are the number of days to be weighted for the average
-20 is the stock quantity moved at the considered line
MINDBLOWING for me
Sorry but I still don't see the pattern here to create a Measure...
((150*24)+((150-20)*36)/60 ???
Show us what the result should look like in your Excel table?
