Average days in Inventory. FIFO

[lisamariegp7]

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

[lisamariegp7]

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,

[v-juanli-msft]

Hi @lisamariegp7 

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

Also every cells in Column E has different formulas.

I don't understand the calculation rule.

Is your formula like something here?

\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{alignedBest Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lisamariegp7]

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,

[v-juanli-msft]

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

[amitchandak]

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

[v-juanli-msft]

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

[Ashish_Mathur]

Hi,

Share a dataset and show the expected result.