Calculation of Moving Average depending on the date and other value in the same row

[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!

[aar0n]

I am hoping to do a very similar situation - you have it for a 30 day time frame, but the only difference is that i need it to take the average of every value - up to that row!

Any suggestions?

[v-jiascu-msft · Accepted solution]

Hi @johnsonrs,

You could try this formula. 

AverageCol =
CALCULATE (
    AVERAGE ( TableA[SALES] ),
    DATESINPERIOD ( 'Calendar'[Date], 'TableA'[DATE], -30, DAY ),
    ALLEXCEPT ( TableA, TableA[SKU] )
)

One note: If the data is 2017-01-01  100, 2017-01-30  200, the average is (100 + 200) / 2 = 150. NOT (100 + 200) / 30 = 10.

 

Best Regards!

Dale