Inventory Analysis- Average Weekly Sales

[bvillegbr]

Hello all!

I am relatively new to PowerBI and had a couple of questions. I am doing an inventory analysis where i have to do the following : 

Calculate average weekly sales.

I have created a filter where ti allows the users to input how many weeks back they want the calculation/filter to take them. That's based on the following columns: 

Posting Date = Date of transaction

Week of Year = Week of posting date

Custom = DateTime.Date(DateTime.LocalNow())

*  converted custom to a week of year value

Weekly Filter = Custom-Week of Year

I ended up using this as a filter where 0= this week, 1= last week , 2 = 2 weeks ago, etc. 

Now I needed to calculate the average sals per week for the items. I used the following columns/measures. :

Quantity = quantity of items sold

Weekly Filter = Custom-Week of Year

Quantity Average by Weekly Filter = Average X( KEEPFILTERS(VALUES( 'CUSTOMER AND SALES INVOICE LINE MERGE'[WEEKLY FILTER])), CALCULATE (SUM('CUSTOMER AND SALES INVOICE LINE MERGE'[QUANTITY])))

The problem with this is that it is calculating the average only based on the weekly filter where that item has value. For example =, item 5196 was only purchased in 28 of the 35 weeks input for the filter so it is only diving the quantity by the 28 rather than the 35(when the product was first purchased by customer). I want to calcualte weekly average since the product/item was introduced S if the item was introduced 35 weeks ago but only sold in 28 of those weeks I want it to calculate the average over 35(date/week product was first sold in). 

Would this be possible? I want it to be able to update with information from a SQL server so would this be a fix that has to be input into the SQL server?

I look forward to hearing any ideas on how to go about this hurdle! 🙂 

Best, 

BV

EDIT :I forgot to mention the data is structured based on the Document No. So there are multiple items for a sales invoice. For example  : 

Doc_No	Item No	Customer	Posting Date	Quantity
SI1031	5379	X	1/1/2020 12:00:00AM	36
SI1031	5390	X	1/1/2020 12:00:00AM	90
SI1031	5385	X	1/1/2020 12:00:00AM	60

Each SI is tied to a specific customer and item. Many items for a single SI and single customer. 

[Anonymous]

To make time-based calculations easy you have to structure your data correctly in the first place. The model should have a proper calendar, first of all, and it should follow the star schema. If you do it the right way, you'll have a much easier time calculating what you want. Please learn about the star schema and its importance for Power BI modeling: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema