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!

[daxer-almighty]

If you've got problems with DAX, then it's time to revise the model. If your model is correct and simple, so will be the DAX. Please use Power Query to massage your data into a good and simple model. Just stick to the star schema. If your tables are missing certain records, then add them using Power Query. Make your fact tables consistent. Do not leave gaps as it's much harder to work with data that is incomplete and the DAX will be complex and slower.