Forecasting future month using previous 3 month average

[Anonymous]

Hi All!
I am trying to create a measure for run rate. For the closed month, the run rate is [Actual Sales] - [SFDC Actual]. For months not closed yet, it would be the average for previous 3 months run rate. How to calculated the forecast run rate to the end of the year.

For example,

Year	Month	Region	SFDC secured amount	Actual	Run Rate	Run Rate 3M
2017	October	A	3,000	5,300	2,300	2,300
2017	November	A	4,000	5,500	1,500	3,800
2017	December	A	2,000	6,000	4,000	7,800
2018	January	A	3,500	7,000	3,500	9,000
2018	February	A	2,900	5,300	2,400	9,900
2018	March	A	5,000	7,000	2,000	7,900
2018	April	A	3,000	5,500	2,500	6,900
2018	May	A	3,200	6,000	2,800	7,300
2018	June	A	3,500	5,000	1,500	6,800
2018	July	A	2,400	5,000	2,600	6,900
2018	August	A	4,800	6,000	1,200	5,300
2018	September	A	2,900	5,000	2,100	5,900
2018	October	A	3,200	4,500	1,300	4,600
2018	November	A	3,000	5,400	2,400	5,800
2018	December	A	1,000			3,700
2019	January	A	0			2,400
2019	February	A	0			0
2019	March	A	0			0
2019	April	A	0			0
2019	May	A	0			0
2019	June	A	0			0
2019	July	A	0			0
2019	August	A	0			0
2019	September	A	0			0

Dec is not completed yet, I need the run rate for Dec to be 5800/3, and Jan run rate would be based on the result of Dec run rate, Nov run rate and Oct run rate(5800/3 + 2400+ 1300)/3, and so forth for the rest of the month in the fisical year. 

Appreciate the help!

Zongze 

[v-danhe-msft]

Hi @Anonymous,

From your description, you could refer to below stpes to sum previous month value:

Add an index column in query editor:

Create below measure:

Measure = CALCULATE(SUM(Table1[Run Rate]),FILTER(ALL('Table1'),'Table1'[Index]<=MAX('Table1'[Index])&&'Table1'[Index]>=MAX('Table1'[Index])-2))

Result:

But I could not figure out how could you want to calculate for 'Run Rate ' in 2018 December? Due to the value in Power BI is based on one whole column, you could not just calculate for different rows.

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

Thank you @v-danhe-msft for your reply!

I didn't just want to calculate run rate for December 2018, I am trying to calculate the run rate for all the months remaining in the date list, using the average of previous 3 months. For example, Jan 2019 run rate = (Oct 2018 + Nov 2018 + Dec 2018)/3, Feb 2019 = (Nov 2018 + Dec 2018+ Jan 2019)/3. I can only get as far as Dec 2018, is there any way I can calculate the rest of the months? 

Thank you. Really appreciate your help.

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

Based on my test, I am afraid you could not calculate the rest of the months due to you must have indeed data for your rest of month.

Regards,

Daniel He

[Anonymous]

Hi @v-danhe-msft,

I appreciate all of your help!

Zongze

[VirLE]

Hello,

Have you found any solution that works for this situation?

I have the same request and I am struggling for 3 days already to make it work. I can just allocate the average of the last 3 months with data, to the rest of the future months, but what I actually need is a dynamic cascading of the 3 months average for future months. I think this is what you are also trying to achieve.

Thank you!

[v-danhe-msft]

Hi @Anonymous,

It's pleasant that your problem has been solved, could you please mark my reply as Answered to close this topic?

Regards,

Daniel He