cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Average days in Inventory. FIFO

Hello,

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

 

Thanks

 

7 REPLIES 7
Highlighted
Super User IV
Super User IV

Re: Average days in Inventory. FIFO

Hi,

Share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Community Support
Community Support

Re: Average days in Inventory. FIFO

Hi @lisamariegp7 

Could you show some data example?

Please use some example to show "purchase N, sales item FIFO, inventory at hand" so i can make a test.

 

Best Regards

Maggie

Highlighted
Super User IV
Super User IV

Re: Average days in Inventory. FIFO

@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...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Average days in Inventory. FIFO

Hi @amitchandak , @v-juanli-msft , @Ashish_Mathur 

 

Here's a link with a sample dataset of what I'm trying to do.

https://drive.google.com/open?id=1yFP0WlZkJE2USgeRYWuHevMGZ7q0sJn4maEY4c2OYC4

Column E has the Average days of Inventory formula I'm trying to create.

 

Thanks,

 

Highlighted
Community Support
Community Support

Re: Average days in Inventory. FIFO

Hi @lisamariegp7 

As tested, the cell E3 should be 1 based on your formula.

Capture12.JPGCapture13.JPG

Also every cells in Column E has different formulas.

I don't understand the calculation rule.

Is your formula like something here?

Capture14.JPG

\begin{aligned} &DSI = \frac{\text{Average inventory}}{COGS} \times 365 \text{ days}\\ &\textbf{where:}\\ &DSI=\text{days sales of inventory}\\ &COGS=\text{cost of goods sold}\\ \end{aligned

 

Highlighted
Frequent Visitor

Re: Average days in Inventory. FIFO

Hello @v-juanli-msft 

 

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. 

https://drive.google.com/open?id=1yFP0WlZkJE2USgeRYWuHevMGZ7q0sJn4maEY4c2OYC4

Thanks,

Highlighted
Community Support
Community Support

Re: Average days in Inventory. FIFO

Hi @lisamariegp7 

Sorry, i still can't solve this problem.

I search some documents how to use DAX to calculate Inventory or Stock Valuation using FIFO.

 

Best Regards

Maggie

 

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors