Forum Discussion
Display Average Monthly Sales Quantity
Hi,
Im looking into displaying avarage montly sales depending on the amount of months a product has been sold.
I want to show the SKU, Current inventory and Average monthly sales quantity in a column of their own. I already have SKU and current inventory but need to calculate the avarage monthly sales from a transactions table like below. How can i go about this?
If possbile i would like to use this data also for calculating a forecast of sales in the coming months.
| SKU | Current Inventory | Monthly Average Sales Quantity |
| EXT-KIT-82 | 56 | x |
My Transaction table looks as following.
| Date | Transaction Type | SKU | Quantity |
| 2023-01-23 | Sale | EXT-KIT-82 | -10, |
| 2023-01-23 | Sale | EXT-KIT-82 | -4, |
| 2023-01-20 | Sale | EXT-KIT-82 | -1, |
| 2023-01-20 | Sale | EXT-KIT-82 | -2, |
| 2023-01-19 | Sale | EXT-KIT-82 | -5, |
| 2023-01-19 | Sale | EXT-KIT-82 | -1, |
| 2023-01-19 | Sale | EXT-KIT-82 | -1, |
| 2023-01-18 | Sale | EXT-KIT-82 | -4, |
| 2023-01-18 | Sale | EXT-KIT-82 | -1, |
| 2023-01-17 | Sale | EXT-KIT-82 | -6, |
| 2023-01-16 | Sale | EXT-KIT-82 | -1, |
| 2023-01-16 | Sale | EXT-KIT-82 | -1, |
| 2023-01-16 | Sale | EXT-KIT-82 | -1, |
| 2023-01-16 | Sale | EXT-KIT-82 | -1, |
| 2023-01-16 | Sale | EXT-KIT-82 | -5, |
| 2023-01-13 | Sale | EXT-KIT-82 | -55, |
| 2023-01-13 | Sale | EXT-KIT-82 | -2, |
| 2023-01-12 | Sale | EXT-KIT-82 | -8, |
3 Replies
- amitchandak
Super User
Hi,
Thank you for the suggestion. I tried the following measure.
Average Monthly Demand = AVERAGEX(VALUES((Calender[Date])),CALCULATE(SUM(Yellow_ItemLedgerEntryBI[Quantity])))
But it just displays the current stock level instead of the monthly average.
I used my Calender date table which is not the in the same table as the posting date of the actual transaction. Is this correct? For my Date table i used CALENDERAUTO(12), could this be a factor?
Could it have something to do with my quantity in transaction list being negative values? If i use a list of absolute values would that work?
