Forum Discussion
Forecasting future month using previous 3 month average
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
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
6 Replies
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
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.
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