cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mboucher_rcr
Regular Visitor

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:

DateItemStoreSOH
7/02/2022A1Melbourne50
7/02/2022A1Sydney90
14/02/2022A1Melbourne60
14/02/2022A1Sydney100
21/02/2022A1Melbourne40
21/02/2022A1Sydney120
28/02/2022A1Melbourne55
28/02/2022A1Sydney80
7/03/2022A1Melbourne20
7/03/2022A1Sydney70
14/03/2022A1Melbourne10
14/03/2022A1Sydney80
21/03/2022A1Melbourne5
21/03/2022A1Sydney100
28/03/2022A1Melbourne50
28/03/2022A1Sydney80
4/04/2022A1Melbourne40
4/04/2022A1Sydney90
7/02/2022B2Melbourne10
7/02/2022B2Sydney500
14/02/2022B2Melbourne10
14/02/2022B2Sydney400
21/02/2022B2Melbourne5
21/02/2022B2Sydney410
28/02/2022B2Melbourne0
28/02/2022B2Sydney350
7/03/2022B2Melbourne50
7/03/2022B2Sydney500
14/03/2022B2Melbourne45
14/03/2022B2Sydney450
21/03/2022B2Melbourne40
21/03/2022B2Sydney100
28/03/2022B2Melbourne35
28/03/2022B2Sydney150
4/04/2022B2Melbourne50
4/04/2022B2Sydney150

 

Required Results:

 

Average Total Inventory by Product per Month

 FebruaryMarchApril
A174.37551.87565
B2195.83171.25100

 

Last/Latest Total Inventory by Product per Month

 FebruaryMarchApril
A1135130130
B2350185200

 

Hoping someone can point me in the right direction to achieve this! Thanks 😁

3 REPLIES 3
Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

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?


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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors