Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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,
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.
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,
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
@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...
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
Hi,
Share a dataset and show the expected result.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |