Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
You may use LOOKUPVALUE Function to add a calculated column or refer to this example.
@v-chuncz-msft the LOOKUPVALUE will not work as the annual figures first need to be split monthly according to the distribution.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |