I have a table of a all items in inventory each with their purchase dates, items sales, purchase cost and inventory stock at hand. I am trying to calculate the average days in inventory of these items using FIFO. In other words I need the formula to work something like this:
Average days in inventory=((no of items purchased in purchase N - sales item FIFO) * number of days in inventory)/inventory at hand
@lisamariegp7 ,Can you share sample data and sample output.
I am hosting a webinar on 25th April on Power BI, Check Details - https://www.linkedin.com/posts/amitchandak78_webinar-tech-techforgood-activity-6658266754378231808-y...
Here's a link with a sample dataset of what I'm trying to do.
Column E has the Average days of Inventory formula I'm trying to create.
Every cell has a different formula because I want to determine the age of the inventory taking into account the order in which the product(s) entered. Is the same thing as if I buy a product in different dates and the selling price of the product is different. On sheet2 of the following link I've created an example doing what I want to do but instead of using dates I used price. In column F you can see the formulas I used. Just like the previous example there's in not a constant formula because it depends on the date the product was bought.
Visit our Community Blog for articles, guides, and information created by fellow community members.
Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.
Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications
Features releasing from October 2020 through March 2021