Morning. I'm trying to make a calculation, I try to explain it below with some images, I leave a small set of data only as an example and also the attempts I made by their can be helpful. This is my dataset (example, which is available in .csv format in the attachments area)
As you can see, each line represents the state of stock for a particular batch/warehouse/date. There are only lines for those dates in which that batch/warehouse pair has undergone some change in its stock (so there is not a line for each date). My ultimate goal is to obtain an average level of stock from all lots in all warehouses. For this, I believe, the procedure I must follow is:
1/ Get a table showing the available stock for each batch/warehouse pair at maximum date, I think the result should be something like this:
*Note as for the case of lot 1400 with warehouse 2 I have taken by default the last line, understanding that this field can come with complete date+time format that allows me to take the last effective record.
2/ Once obtained this table make an avg() with which to calculate the KPI.