Quarter beginning Inventory

[H20treddr]

I am trying to measure beginning inventory for a Month or Quarter.  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.  Is there a way to do this with measures and/or DAX?  THANKS

		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

[AnthonyTilley]

Do You have some Sample data.

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. 

please add a sample data set and i can take a look for you.

[H20treddr]

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

[Anonymous]

HI @H20treddr ,

You can consider to write a rolling total formula to summary multiple date periods.

DAX 101: Computing running totals in DAX

Regards,

Xiaoxin Sheng