Quarter beginning Inventory

Question

I am trying to measure beginning inventory for a Month or Quarter.&nbsp; The difficulty is that in any given week I do not necessarilly have data (see table) so I woud like to summ across the "first reported" week.&nbsp; Is there a way to do this with measures and/or DAX?&nbsp; THANKS&nbsp;&nbsp;&nbsp;qtr 1qtr 1qtr 1qtr 1qtr 1customer nameSKUweek 1week 2week 3week 4week 5Customer 1SKU 11&nbsp;&nbsp;&nbsp;1&nbsp;SKU 21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SKU 31&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SKU 41&nbsp;&nbsp;&nbsp;&nbsp;Customer 2SKU1&nbsp;111&nbsp;Customer 3SKU1&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;SKU 1&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;SKU 2&nbsp;&nbsp;&nbsp;1&nbsp;&nbsp;SKU 3&nbsp;&nbsp;&nbsp;1&nbsp;Customer 4SKU 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Customer 5SKU 2&nbsp;1&nbsp;11Customer 6SKU 31&nbsp;&nbsp;&nbsp;&nbsp;Customer 7SKU 3&nbsp;&nbsp;11&nbsp;Customer 8SKU 2111&nbsp;&nbsp;Customer 9SKU 1&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Customer 10SKU 1&nbsp;1111&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Beginning Value for qtr 1 = Sum of red = 14&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Answer

HI&nbsp;H20treddr&nbsp;,
You can consider to write a rolling total formula to summary multiple date periods.
DAX 101: Computing running totals in DAX
Regards,
Xiaoxin Sheng

anthonytilley · Answer

Do You have some Sample data.&nbsp;I have done this before the idea is to Calculate toe sum or values for each week, find the first week and return this value. these two parts are relativly simple but it is dependent on how your data is structured in the data table.for example if you have two colunms of Week and Qrt in your table then this complicates things as you cannot use time based functions.&nbsp;&nbsp;please add a sample data set and i can take a look for you.

h20treddr · Answer

Thanks Anthony - we are the more complicated :) ... here is some sample data:

Region	Reporting Customer	Product SKU	Year	Week Number	Month Number	Qtr Number	Sales Quantity
Region A	Customer 1	SKU 1	2018	50	12	4	42
Region A	Customer 1	SKU 1	2018	51	12	4	2
Region A	Customer 1	SKU 2	2018	51	12	4	1
Region A	Customer 1	SKU 1	2019	2	1	1	42
Region A	Customer 1	SKU 1	2019	3	1	1	41
Region A	Customer 2	SKU 1	2019	4	1	1	1
Region A	Customer 1	SKU 1	2019	4	1	1	41
Region A	Customer 1	SKU 1	2019	5	1	1	1
Region A	Customer 1	SKU 2	2019	5	2	1	40
Region A	Customer 2	SKU 1	2019	5	1	1	4
Region A	Customer 3	SKU 3	2019	6	2	1	1
Region A	Customer 1	SKU 1	2019	6	2	1	40
Region A	Customer 1	SKU 1	2019	7	2	1	40
Region A	Customer 2	SKU 1	2019	7	2	1	4
Region A	Customer 1	SKU 1	2019	8	2	1	40
Region A	Customer 2	SKU 1	2019	8	2	1	8
Region A	Customer 3	SKU 3	2019	9	2	1	1
Region A	Customer 1	SKU 1	2019	9	3	1	40
Region A	Customer 2	SKU 1	2019	9	2	1	3
Region B	Customer 4	SKU 1	2019	9	2	1	1
Region A	Customer 1	SKU 1	2019	10	3	1	40
Region B	Customer 4	SKU 1	2019	10	3	1	1
Region A	Customer 1	SKU 1	2019	11	3	1	40
Region A	Customer 2	SKU 1	2019	11	3	1	3
Region B	Customer 4	SKU 1	2019	11	3	1	1

Forum Discussion

Quarter beginning Inventory

3 Replies

Related content

Beginning and Ending Inventory Balance

Quarter over quarter inventory

Inventory and Stock balance calculation

Inventory Change from Beginning Value

Inventory Forecast

		qtr 1	qtr 1	qtr 1	qtr 1	qtr 1
customer name	SKU	week 1	week 2	week 3	week 4	week 5
Customer 1	SKU 1	1				1
	SKU 2	1
	SKU 3	1
	SKU 4	1
Customer 2	SKU1		1	1	1
Customer 3	SKU1				1
	SKU 1				1
	SKU 2				1
	SKU 3				1
Customer 4	SKU 1
Customer 5	SKU 2		1		1	1
Customer 6	SKU 3	1
Customer 7	SKU 3			1	1
Customer 8	SKU 2	1	1	1
Customer 9	SKU 1
Customer 10	SKU 1		1	1	1	1

Beginning Value for qtr 1 = Sum of red = 14