Hi all, I would like to request assistance to get the annual budget split over months with seasonality. So, what I have is the prior 3 Years actual figures with different Items (only one Year illustrated below). These I would like to split for the new budget year as an average of the prior 3 years trends. Here is a sample of the prior year’s actuals per item:
| Period | Item | Actuals | % |
| 201607 | 111 | 24751 | 7.5% |
| 201608 | 111 | 25778 | 7.8% |
| 201609 | 111 | 25688 | 7.8% |
| 201610 | 111 | 25968 | 7.9% |
| 201611 | 111 | 25577 | 7.8% |
| 201612 | 111 | 25496 | 7.8% |
| 201701 | 111 | 27467 | 8.4% |
| 201702 | 111 | 28947 | 8.8% |
| 201703 | 111 | 29095 | 8.9% |
| 201704 | 111 | 27812 | 8.5% |
| 201705 | 111 | 29078 | 8.9% |
| 201706 | 111 | 32726 | 10.0% |
| 201607 | 125 | 24412 | 7.3% |
| 201608 | 125 | 25425 | 7.6% |
| 201609 | 125 | 25336 | 7.6% |
| 201610 | 125 | 29454 | 8.8% |
| 201611 | 125 | 29011 | 8.7% |
| 201612 | 125 | 25650 | 7.7% |
| 201701 | 125 | 27632 | 8.3% |
| 201702 | 125 | 29122 | 8.7% |
| 201703 | 125 | 29270 | 8.7% |
| 201704 | 125 | 27980 | 8.4% |
| 201705 | 125 | 29253 | 8.7% |
| 201706 | 125 | 32277 | 9.6% |
| 201607 | 131 | 495 | 7.5% |
| 201608 | 131 | 516 | 7.8% |
| 201609 | 131 | 514 | 7.8% |
| 201610 | 131 | 519 | 7.9% |
| 201611 | 131 | 512 | 7.8% |
| 201612 | 131 | 510 | 7.8% |
| 201701 | 131 | 549 | 8.4% |
| 201702 | 131 | 579 | 8.8% |
| 201703 | 131 | 582 | 8.9% |
| 201704 | 131 | 556 | 8.5% |
| 201705 | 131 | 582 | 8.9% |
| 201706 | 131 | 655 | 10.0% |
Then I have a sample of the Budget for the next financial year as one value only for the year. This I would like to split as per the performance of the average of the prior years, e.g.
| Budget Year | Item | Budget |
| 2017/2018 | 111 | 394058 |
| 2017/2018 | 125 | 411829 |
| 2017/2018 | 131 | 8538 |
So, the desired outcome in Power BI automatically adjusting would be calculating the seasonality or monthly trend from prior years multiplied by the annual budget figure to illustrate the monthly budget e.g.
| Period | Item | Seasonality | Monthly Budget |
| 201707 | 111 | 7.5% | 29,701 |
| 201708 | 111 | 7.8% | 30,933 |
| 201709 | 111 | 7.8% | 30,825 |
| 201710 | 111 | 7.9% | 31,161 |
| 201711 | 111 | 7.8% | 30,692 |
| 201712 | 111 | 7.8% | 30,595 |
| 201801 | 111 | 8.4% | 32,960 |
| 201802 | 111 | 8.8% | 34,737 |
| 201803 | 111 | 8.9% | 34,913 |
| 201804 | 111 | 8.5% | 33,375 |
| 201805 | 111 | 8.9% | 34,893 |
| 201806 | 111 | 10.0% | 39,271 |
| 201707 | 125 | 7.3% | 30,027 |
| 201708 | 125 | 7.6% | 31,272 |
| 201709 | 125 | 7.6% | 31,163 |
| 201710 | 125 | 8.8% | 36,228 |
| 201711 | 125 | 8.7% | 35,683 |
| 201712 | 125 | 7.7% | 31,549 |
| 201801 | 125 | 8.3% | 33,988 |
| 201802 | 125 | 8.7% | 35,820 |
| 201803 | 125 | 8.7% | 36,002 |
| 201804 | 125 | 8.4% | 34,415 |
| 201805 | 125 | 8.7% | 35,981 |
| 201806 | 125 | 9.6% | 39,701 |
| 201707 | 131 | 7.5% | 644 |
| 201708 | 131 | 7.8% | 670 |
| 201709 | 131 | 7.8% | 668 |
| 201710 | 131 | 7.9% | 675 |
| 201711 | 131 | 7.8% | 665 |
| 201712 | 131 | 7.8% | 663 |
| 201801 | 131 | 8.4% | 714 |
| 201802 | 131 | 8.8% | 753 |
| 201803 | 131 | 8.9% | 756 |
| 201804 | 131 | 8.5% | 723 |
| 201805 | 131 | 8.9% | 756 |
| 201806 | 131 | 10.0% | 851 |
Your assistance in getting these calculations done in Power BI would be truly appreciated.
