cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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 3
Super User

@Bondeuz , try a measure like

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

Frequent Visitor

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?

Frequent Visitor

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?

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors