Automate the turnover calculation

[Angie_]

Hi, 

May i know how to use power BI to create an automation to calculate the turnover repriod of my inventory.

For example, I have the data for my monthly purchase and the ending inventory. How do I automate using power BI to help me calculate the turnover for every month? (total up of how many months of purchase in order to cover the particular month ending inventory)

As i have multiple items and the manual way I'm doing is to sum up my total puchase in order to cover the ending inventory. Like for example the manual calculation for Jun'21:

Item "A" - sum up the purchase from Apr'20 to Jun'21 ($3.2M) in order to cover the ending inventory balance for Jun'21 ($3M). So the turnover for Item A for Jun'21 is 15 months.

Is there any way to automate this rather than calculating it item by item and month by month? As every month the particular item will have different turnover depands on the Purchase amount and the balance remaining as inventory.

Example from below table, is the manual way i use to calculate the Turnover, from here can see different item and every month will have different turnaround.

Thank you.

Item #	Month	Total Purchase	Ending Inventory	Turnover
A	JAN-20	500,000	4,000,000
	FEB-20	200,000	4,000,000
	MAR-20	400,000	4,000,000
	APR-20	400,000	4,000,000
	MAY-20	200,000	4,000,000
	JUN-20	200,000	4,000,000
	JUL-20	100,000	4,000,000
	AUG-20	200,000	4,000,000
	SEP-20	400,000	4,000,000
	OCT-20	400,000	4,000,000
	NOV-20	200,000	4,000,000
	DEC-20	200,000	4,000,000
	JAN-21	100,000	4,000,000
	FEB-21	100,000	4,000,000
	MAR-21	300,000	4,000,000
	APR-21	100,000	4,000,000	16
	MAY-21	200,000	3,000,000	14
	JUN-21	100,000	3,000,000	15
B	JAN-20	22,900,000	154,000,000
	FEB-20	22,600,000	155,000,000
	MAR-20	11,100,000	157,000,000
	APR-20	7,200,000	158,000,000
	MAY-20	19,300,000	165,000,000
	JUN-20	18,600,000	166,000,000
	JUL-20	18,500,000	167,000,000
	AUG-20	18,500,000	169,000,000
	SEP-20	16,200,000	166,000,000
	OCT-20	21,300,000	161,000,000	10
	NOV-20	17,200,000	163,000,000	10
	DEC-20	18,500,000	161,000,000	10
	JAN-21	17,300,000	161,000,000	9
	FEB-21	17,600,000	162,000,000	9
	MAR-21	22,000,000	162,000,000	9
	APR-21	21,800,000	161,000,000	9
	MAY-21	17,800,000	159,000,000	9
	JUN-21	20,000,000	160,000,000	9
C	JAN-20	2,100,000	14,000,000
	FEB-20	2,300,000	13,000,000
	MAR-20	1,500,000	13,000,000
	APR-20	800,000	13,000,000
	MAY-20	2,100,000	13,000,000
	JUN-20	2,400,000	13,000,000
	JUL-20	2,500,000	13,000,000	7
	AUG-20	2,100,000	13,000,000	7
	SEP-20	2,900,000	13,000,000	7
	OCT-20	2,900,000	13,000,000	6
	NOV-20	2,700,000	13,000,000	5
	DEC-20	2,500,000	13,000,000	5
	JAN-21	2,100,000	13,000,000	5
	FEB-21	2,000,000	13,000,000	6
	MAR-21	2,800,000	13,000,000	6
	APR-21	2,100,000	12,000,000	6
	MAY-21	2,000,000	12,000,000	6
	JUN-21	2,300,000	12,000,000	6

[amitchandak]

@Angie_ , with help from date tbale you can try measures like

Rolling 15 = CALCULATE(sum(Table[Ending Inventory]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-15,MONTH))

or

Avg Inv = AverageX(Values(date[Month year]) , calculate(sum(Table[Ending Inventory]))) //ot endofmonth inventory

Rolling 15 = CALCULATE([Avg Inv],DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-15,MONTH))