Calculate cycle of order from the last 12 months from the last sales

[olives]

Hi,

I am trying to create a calculated column to show the cycle of order (number of months) that two products (A and B) are ordered in each store.

I have a "Store" table, "Sales" table and "Calender" table.

I would like to start calculating from the last 12 months from the last sale.

sales

store_cd	QTY	date	item
200	1	2021/10/1	C
200	5	2021/11/1	A
200	3	2021/12/1	A
200	5	2022/2/1	C
200	11	2022/5/1	A
200	2	2022/7/1	B
200	2	2022/10/1	A
200	2	2022/10/1	C
200	3	2022/11/1	A
200	6	2022/12/1	A
200	-6	2022/12/1	A

stores

store_cd	store_name
200	StoreA
201	StoreB
202	StoreC
203	StoreD

For the result, I would like to get 2.4 (12month /5 times) for store 200.

Please help!

[lbendlin · Accepted solution]

[olives]

@lbendlin 
Thank you so much for your help!
It worked😊