Average filtered on values in the same row as new column like sumifs in excel

[johnsonrs]

Hey everyone, 

I already red quite a bit in the community but I did not find the proper solution to my problem.

I have a table with following columns

SKU, DATE, STOCKLEVEL, SALES

With contains data on a daily level for different SKU.

Now I want to calculate the Average Sales based on the date and the SKU in ONE row and save that in a new column. I what to that to as a column because I what to be able to filter over it afterwards.

SKU, DATE, STOCKLEVEL, SALES, NEWCOLUMN

A - 01.01.2017- 100 - 10 - Average Sales for the Product A in the last 30 days before the 01.01.2017

I also have an seperate date table and a table containing the sales per SKU, and date if this might help.

Thanks in advance!

[v-caliao-msft]

@johnsonrs,

Could you please provide us more sample data and elaborate the expected results with some samples, so that we can make further analysis.

Regards,

Charlie Liao

[johnsonrs]

Hey @v-caliao-msft,

thanks for your Replay. My Data looks like this.  My Column "Avg last 60d" is the one where I have the Problems with:

Avg last 60d = CALCULATE(AVERAGEX(ALLSELECTED(Stock);Stock[Sales]);DATESINPERIOD(Stock[SNAPSHOT_DATE];LASTDATE(Stock[SNAPSHOT_DATE]);-60;DAY);Stock[SKU]="e055")

Just for demonstation is hard plugged the value of one Product, i.e e055 but my goal is that at every line the the value from the column SKU in the same row will be taken instead of the value I just filled in. As you can see in the screenshot, the list contains different Products (= SKU) and hence only the sales of this product of the last 60d shall be used to calculate the average.  In other words every single cell in the Avg Last 60d Column shall contain the avg sales for the last 60 days but only the product (=SKU) in that row and only the last 60 days starting from the date in the same row. 

Thanks again for your effort!