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 days until the numbers of units sold are greater than units on the pallet.
I tried to make a measure for this with help of ChatGPT, but it does not work as intended and at best only in the first instance a pallet is sold out. I also tried making calculated columns, but there is no relation between FacStoreOrder and FacSales. Any suggestions?
Measure:
Measure:
AverageWeeksPalletSales=
VAR AverageWeeks = DIVIDE([#Weeks], [SumPalletsSold])
RETURN
AverageWeeks * [#StoresReceivedProduct]
Sample data:
Dim_Article Dim_Store Dim_Calendar Fac_Sales Measure Dim_Article Fac_OrderStore Measure
| Article | Store | WeekNumber | SumUnitsSold | SumPalletsSold | ArticlePalletUnits | ReceivedPallets | AverageWeeksPalletSales |
|---|---|---|---|---|---|---|---|
| ProductX | X | 10 | 23 | 0.19 | 120 | 1 | 5.22 |
| ProductX | X | 11 | 41 | 0.34 | 120 | ||
| ProductX | X | 12 | 21 | 0.18 | 120 | ||
| ProductX | X | 13 | 23 | 0.19 | 120 | ||
| ProductX | X | 14 | 17 | 0.14 | 120 |