Forum Discussion
Monthly Inventory Value
Hi,
I have data with weekly inventory values per product and store. What I need to get to is the below 2 measures:
1. Average Total Inventory by Product per Month
2. Last/Latest Total Inventory by Product per Month
Sample source data:
| Date | Item | Store | SOH |
| 7/02/2022 | A1 | Melbourne | 50 |
| 7/02/2022 | A1 | Sydney | 90 |
| 14/02/2022 | A1 | Melbourne | 60 |
| 14/02/2022 | A1 | Sydney | 100 |
| 21/02/2022 | A1 | Melbourne | 40 |
| 21/02/2022 | A1 | Sydney | 120 |
| 28/02/2022 | A1 | Melbourne | 55 |
| 28/02/2022 | A1 | Sydney | 80 |
| 7/03/2022 | A1 | Melbourne | 20 |
| 7/03/2022 | A1 | Sydney | 70 |
| 14/03/2022 | A1 | Melbourne | 10 |
| 14/03/2022 | A1 | Sydney | 80 |
| 21/03/2022 | A1 | Melbourne | 5 |
| 21/03/2022 | A1 | Sydney | 100 |
| 28/03/2022 | A1 | Melbourne | 50 |
| 28/03/2022 | A1 | Sydney | 80 |
| 4/04/2022 | A1 | Melbourne | 40 |
| 4/04/2022 | A1 | Sydney | 90 |
| 7/02/2022 | B2 | Melbourne | 10 |
| 7/02/2022 | B2 | Sydney | 500 |
| 14/02/2022 | B2 | Melbourne | 10 |
| 14/02/2022 | B2 | Sydney | 400 |
| 21/02/2022 | B2 | Melbourne | 5 |
| 21/02/2022 | B2 | Sydney | 410 |
| 28/02/2022 | B2 | Melbourne | 0 |
| 28/02/2022 | B2 | Sydney | 350 |
| 7/03/2022 | B2 | Melbourne | 50 |
| 7/03/2022 | B2 | Sydney | 500 |
| 14/03/2022 | B2 | Melbourne | 45 |
| 14/03/2022 | B2 | Sydney | 450 |
| 21/03/2022 | B2 | Melbourne | 40 |
| 21/03/2022 | B2 | Sydney | 100 |
| 28/03/2022 | B2 | Melbourne | 35 |
| 28/03/2022 | B2 | Sydney | 150 |
| 4/04/2022 | B2 | Melbourne | 50 |
| 4/04/2022 | B2 | Sydney | 150 |
Required Results:
Average Total Inventory by Product per Month
| February | March | April | |
| A1 | 74.375 | 51.875 | 65 |
| B2 | 195.83 | 171.25 | 100 |
Last/Latest Total Inventory by Product per Month
| February | March | April | |
| A1 | 135 | 130 | 130 |
| B2 | 350 | 185 | 200 |
Hoping someone can point me in the right direction to achieve this! Thanks 😁
7 Replies
Thanks Ashish. The Last Total Inventory calculation is working well but the Average Total Inventory is not working with my actual data, I'm getting very low figures which don't add up.
You are welcome. I cannot say why my formulas are not working with your data. From the file that i have shared with you, my calculations work fine and tally with the your expected result. How can i help you further?
Hi Ashish,
Im having similiar requirement, can you send me the pbix file, couldnt download the file from onedrive as it got deleted
Regards
AnandHi,
I do not have the files. Share some data, explain the question and show the expected result.
