Calculate average time to sell a pallet of product

[Skajoa]

We sell products in-store and sometimes have display pallets to increase sales. I want to calculate how long it takes to sell pallets of different products to compare effectiveness. Issue is the sales are registered on the individual product only and not related to whether it is from a pallet or not. Therefore I am attempting to calculate sales from pallets based on the date it was delivered and count weeks until the numbers of units sold are greater than units on the pallet.

I tried to make a measure for this, but it only works for the date the pallet was delivered and then stops counting (may need a cumulative sales formula). I also tried making calculated columns, but there is no relation between FacStoreOrder and FacSales. Any suggestions?

Measure:

AverageWeeksPalletSales=
VAR AverageWeeks = DIVIDE([#Weeks], [SumPalletsSold])

RETURN
AverageWeeks * [#StoresReceivedProduct]

Sample data:

Dim_Article	Dim_Store	Dim_Calendar	Fac_Sales	Measure	Dim_Article	Fac_StoreOrder	Measure
Article	Store	WeekNumber	SumUnitsSold	SumPalletsSold	ArticlePalletUnits	ReceivedPallets	AverageWeeksPalletSales
Product X	X	10	23	0.19	120	1	5,22
Product X	X	11	41	0.34	120
Product X	X	12	21	0.18	120
Product X	X	13	23	0.19	120
Product X	X	14	17	0.14	120

[Skajoa]

In the original post the measure will only display sales pace at the week of delivery (5.22). I want it to display pace for all following weeks when there is still product left on the display and the total row to display the time it took to sell the pallet. I have added a column SumRemainingPallets to show when there is still products left on the display at the end of week. 

In this case the total time to sell a pallet was 4.80 weeks, but how can I make a measure that will give me the numbers in red text?

Dim_Article	Dim_Store	Dim_Calendar	Fac_Sales	Measure	Measure	Dim_Article	Fac_StoreOrder	Measure
Article	Store	WeekNumber	SumUnitsSold	SumPalletsSold	SumRemainingPallets	ArticlePalletUnits	ReceivedPallets	AverageWeeksPalletSales
Product X	X	9	30	0.25		120
Product X	X	10	23	0.19	0.81	120	1	5.22
Product X	X	11	41	0.34	0.47	120		2.93
Product X	X	12	21	0.18	0.29	120		5.71
Product X	X	13	23	0.19	0.10	120		5.22
Product X	X	14	17	0.14		120		7.06
Product X	X	15	18	0.15		120
Total			173	1.44		120	1	4.80

[lbendlin]

It's not clear how much of the pallet was available before week 9.  

Consider using a graphical forecasting solution

dashed lines are at 120 and 240 units. So you would empty the second pallet by week 18 etc.

[Skajoa]

Thanks. The reason it is not clear how much is available before week 9 is because there was no pallet. This product is sold both off the shelf and from pallets, but there isn't a pallet in store every week. Hence the issue I have is separating sales originating from pallets from the regular off-shelf sales. The column ReceivedPallets specifies when a pallet is delivered to the store, and I want to count how long it takes from that point until I have sold 120 units. 

[lbendlin]

I'm afraid that without starting inventory (or other inventory data) this is too ambiguous.

[lbendlin]

I tried to make a measure for this, but it only works for the date the pallet was delivered and then stops counting

That is not visible in your sample data.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Skajoa]

As you see in the original post, the measure only display 5.22 on the week of delivery, and does not count the sales pace for the following weeks. I want the measure to display pace for each week there is still remaining product on the pallet and the total row to display the average pace. I have added a helping column called SumRemainingPallets to display how much of the pallet is left at the end of week. 

Dim_Article	Dim_Store	Dim_Calendar	Fac_Sales	Measure	Measure	Dim_Article	Fac_StoreOrder	Measure
Article	Store	WeekNumber	SumUnitsSold	SumPalletsSold	SumRemainingPallets	ArticlePalletUnits	ReceivedPallets	AverageWeeksPalletSales
Product X	X	9	30	0.25		120
Product X	X	10	23	0.19	0.81	120	1	5.22
Product X	X	11	41	0.34	0.47	120		2.93
Product X	X	12	21	0.18	0.29	120		5.71
Product X	X	13	23	0.19	0.10	120		5.22
Product X	X	14	17	0.14		120		7.06
Total			155	1.29		120	1	4.80