Monthly Inventory Value

[mboucher_rcr]

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 😁

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anand_Reddy]

Hi Ashish,

Im having similiar requirement, can you send me the pbix file, couldnt download the file from onedrive as it got deleted

Regards
Anand

[Ashish_Mathur]

Hi,

I do not have the files.  Share some data, explain the question and show the expected result.

[Anand_Reddy]

i have d 365 inventtranstable where it has item out & item in transactions:- 
sample table:-

DATEFINANCIAL	CATEGORY	inventory value
01-01-2024 00:00	pepsi	-10364.27
02-01-2024 00:00	coke	500000000
03-01-2024 00:00	fanta	-69323.41
04-01-2024 00:00	thumsup	-64566.39
05-01-2024 00:00	pepsi	0
06-01-2024 00:00	maaza	566666
07-01-2024 00:00	pepsi	-79063.53

Expected result in powerbi matrix visualization:-

CATEGORY	Jan	feb	mar
pepsi	50000	100000	2000000
coke	40000	60001	50002
maaza	70000	820100	700100
sprite	90000	920100	1000100
thumsup	50000	820100	900100

need to create measure to retrive those monthly snapshot values from daily transactions at the end of month or 1st day of next month( anything is fine).

im getting correct values using this sql query but having trouble to replicate the logic in sql/powerbi:- SELECT SUM(InventoryValue) from MONTHLYINVENTORYVALUE
WHERE CATEGORY= 'pepsi'
AND DATEFINANCIAL <='2024-02-01';

[Ashish_Mathur]

I do not see any link between the input and output tables.  Based on the input table, show the expected result.

[mboucher_rcr]

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.

[Ashish_Mathur]

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?