cancel
Showing results for
Did you mean:
Highlighted
Frequent 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

Accepted Solutions
Microsoft

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

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
Microsoft

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

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.
Member

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

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?

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!