Display Average Monthly Sales Quantity

[Bondeuz]

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,

[amitchandak]

@Bondeuz , try a measure like

AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Quantity]))

[Bondeuz]

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?

[Bondeuz]

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?