Sales forecast phased by month based on average

[T-Joyce]

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

[V-pazhen-msft · Accepted solution]

@T-Joyce 

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]

Pibx link:

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.