cancel
Showing results for
Did you mean:
Frequent Visitor

## Sales forecast phased by month based on average

Hi,

I am in need of some help/advice. I am wanting trying to phase a customer's forecast for the year by month based upon the monthly average of the previous 2 years worth of sales for that customer.

Basically, in my sales data I have a list of all transactions by date, product, customer and quantity for 2018 to 2019. In another table I have a forecast from the customer for 2020. What I would like to do is work out the average of sales for that customer per month from 2018-2019 by month as a percentage and apply that to the 2020 forecast to then phase the forecast out monthly.

 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Total 2018 10 20 20 30 40 30 10 0 10 20 10 0 200 2019 20 10 40 60 50 30 20 30 50 70 20 0 400 Avg. 15 15 30 45 45 30 15 15 30 45 15 0 300 % of total 5 5 10 15 15 10 5 5 10 15 5 0 2020 500

The above table is a simple example of what I have and what I need to work out.

Is this something that is possible?

Many thanks in advance for any help.

Cheers

Terry

1 ACCEPTED SOLUTION
Community Support

I made a sample based on your table, the result can be something like this:

``````AVG by month = CALCULATE(AVERAGE('Table'[Value]), FILTER('Table',[Date].[Month]= EARLIER('Table'[Date].[Month])))
TotalAVG = SUM('Table'[AVG by month])/2
&AVG = 'Table'[AVG by month]/'Table'[totalAVG]``````

https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EWBEoFHfLNdKpHGYXqOt...

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

I made a sample based on your table, the result can be something like this:

``````AVG by month = CALCULATE(AVERAGE('Table'[Value]), FILTER('Table',[Date].[Month]= EARLIER('Table'[Date].[Month])))
TotalAVG = SUM('Table'[AVG by month])/2
&AVG = 'Table'[AVG by month]/'Table'[totalAVG]``````

https://qiuyunus-my.sharepoint.com/:u:/g/personal/paul_qiuyunus_onmicrosoft_com/EWBEoFHfLNdKpHGYXqOt...

Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks