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

Regular Visitor

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

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.

1 ACCEPTED SOLUTION
Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2

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?

Microsoft Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.