How to get the average stock through movements?

[matteomatilde]

Hi everyone.

I've got this DB with multiple items and 4 warehouses.

In the FACT_STK there are the "registrations" of the current level of the stock for the items (not every item, only some of them), and the dates in this table are from 22 November 2020 to 27 April 2021.

In the FACT_MOV I've got the movements of all items (sales, transfers and returns) with their specificated quantities and warehouses, and the dates are from 2 January 2018 to 27 April 2021.

How can I calculate the average stock, given by 

[sum of (stk_qty * number_of_days_of_stock)] divided by (total days of stock) ?

I've got some problems because the stock quantity is updated only up to a certain date (for example 20 April 2021), but there are other movements (expecially sales) after that date, that are not considered and that don't influence the stock.

This is the link of the pbix file: https://www.dropbox.com/s/9jb2j4ojn3d5vw4/Warehouse.pbix?dl=0 

Thanks for the help!

[v-shex-msft]

Hi @matteomatilde,

Is that any other field that used to define the movement ranges?
If that is the case, you can use this with raw table category 'MOV_WHI_ID' as categories to filter table records, then calculate date diff based on the min and max date. You can use this difference to calculate with the qty you mention.

Regards,
Xiaoxin Sheng